Questions tagged [database]
A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views, and other objects. The data is typically organized to model aspects of reality in a way that supports processes requiring information. Use this tag if you have questions about designing a database. If it is about a particular database management system, (e.g., MySQL), please use that tag instead.
database
195,249
questions
2888
votes
7
answers
1.1m
views
How does database indexing work? [closed]
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?
For information on queries to index a field, check out How ...
- 83.7k
2787
votes
57
answers
5.2m
views
How to import an SQL file using the command line in MySQL?
I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line.
I have a Windows Server 2008 R2 installation. I placed the .sql file on the C ...
- 40k
2503
votes
29
answers
2.9m
views
How to show tables in PostgreSQL?
What's the equivalent to show tables (from MySQL) in PostgreSQL?
- 269k
2290
votes
19
answers
742k
views
What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?
The "N+1 selects problem" is generally stated as a problem in Object-Relational mapping (ORM) discussions, and I understand that it has something to do with having to make a lot of database queries ...
- 25.4k
2193
votes
25
answers
1.5m
views
PostgreSQL "DESCRIBE TABLE"
How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL with psql command?
- 23.3k
1849
votes
27
answers
3.4m
views
Insert into ... values ( SELECT ... FROM ... )
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
- 42.1k
1603
votes
8
answers
341k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1360
votes
24
answers
1.1m
views
Which version of PostgreSQL am I running?
I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server ...
- 39.4k
1156
votes
46
answers
1.6m
views
How do I rename a MySQL database (change schema name)?
How do I quickly rename a MySQL database (change its schema name)?
Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {...
1016
votes
26
answers
1.7m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18.6k
1001
votes
13
answers
639k
views
Optimistic vs. Pessimistic locking
I understand the differences between optimistic and pessimistic locking. Now, could someone explain to me when I would use either one in general?
And does the answer to this question change depending ...
- 196k
923
votes
23
answers
732k
views
Database, Table and Column Naming Conventions? [closed]
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:
Should table names be plural?
Should column ...
- 75.3k
892
votes
25
answers
306k
views
MyISAM versus InnoDB [closed]
I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads ...
- 9,251
885
votes
15
answers
522k
views
What's the difference between identifying and non-identifying relationships?
I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?
- 9,532
870
votes
5
answers
443k
views
Multiple Indexes vs Multi-Column Indexes
What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?
Are there reasons why one should be used over the other?
For example:
Create ...
- 75.3k
815
votes
11
answers
856k
views
How to get size of mysql database?
How to get size of a mysql database?
Suppose the target database is called "v3".
- 8,455
814
votes
10
answers
471k
views
Difference between scaling horizontally and vertically for databases [closed]
I have come across many NoSQL databases and SQL databases. There are varying parameters to measure the strength and weaknesses of these databases and scalability is one of them. What is the difference ...
- 27.9k
765
votes
8
answers
1.6m
views
'IF' in 'SELECT' statement - choose output value based on column values
SELECT id, amount FROM report
I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
- 14.1k
764
votes
6
answers
316k
views
MongoDB vs. Cassandra [closed]
I am evaluating what might be the best migration option.
Currently, I am on a sharded MySQL (horizontal partition), with most of my data stored in JSON blobs. I do not have any complex SQL queries (...
- 27.9k
749
votes
10
answers
931k
views
Create new user in MySQL and give it full access to one database
I want to create a new user in MySQL and give it full access only to one database, say dbTest that I create with a command like create database dbTest;. What would be the MySQL commands to do that?
- 7,507
740
votes
21
answers
941k
views
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run ...
- 30.1k
739
votes
12
answers
433k
views
Postgres: upgrade a user to be a superuser?
In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.
# alter user myuser ...?
- 10.7k
736
votes
24
answers
517k
views
How to drop columns using Rails migration
What's the syntax for dropping a database table column through a Rails migration?
- 9,718
727
votes
6
answers
441k
views
Difference between rake db:migrate db:reset and db:schema:load
The difference between rake db:migrate and rake db:reset is pretty clear to me. The thing which I don't understand is how rake db:schema:load is different from the former two.
Just to be sure that I ...
- 14.8k
727
votes
10
answers
239k
views
How can I avoid concurrency problems when using SQLite on Android?
What would be considered the best practices when executing queries on an SQLite database within an Android app?
Is it safe to run inserts, deletes and select queries from an AsyncTask's ...
- 42.9k
711
votes
22
answers
1.2m
views
Import SQL dump into PostgreSQL database
We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site.
Now, I'm trying to set this up on a local WAMP server to test this.
The only problem is that I don'...
- 8,582
686
votes
11
answers
913k
views
How do you query for "is not null" in Mongo?
I would like to execute a following query:
db.mycollection.find(HAS IMAGE URL)
What should be the correct syntax?
- 268k
682
votes
13
answers
464k
views
How to skip certain database tables with mysqldump?
Is there a way to restrict certain tables from the mysqldump command?
For example, I'd use the following syntax to dump only table1 and table2:
mysqldump -u username -p database table1 table2 > ...
676
votes
16
answers
563k
views
Export schema without data
I'm using a MySql database with a Java program, now I want to give the program to somebody else.
How to export the MySQL database structure without the data in it, just the structure?
- 9,675
675
votes
19
answers
1.1m
views
How do I restore a dump file from mysqldump?
I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.
I tried using MySQL Administrator, but I got the following error:
The selected file was ...
- 56.8k
668
votes
26
answers
521k
views
How to insert multiple rows in SQLite?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 235k
659
votes
11
answers
294k
views
Difference between partition key, composite key and clustering key in Cassandra?
I have been reading articles around the net to understand the differences between the following key types. But it just seems hard for me to grasp. Examples will definitely help make understanding ...
- 30.9k
646
votes
66
answers
607k
views
Postgres could not connect to server
After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well.
This is the error message. (I also got this ...
- 6,623
646
votes
13
answers
535k
views
Copying PostgreSQL database to another server
I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?
- 13.4k
626
votes
29
answers
1.0m
views
Elasticsearch query to return all records
I have a small database in Elasticsearch and for testing purposes would like to pull all records back. I am attempting to use a URL of the form...
http://localhost:9200/foo/_search?pretty=true&q=...
- 31k
618
votes
58
answers
142k
views
Do you use source control for your database items? [closed]
I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to ...
610
votes
25
answers
824k
views
How to get a list of column names on Sqlite3 database?
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.
This Stackoverflow entry suggests doing the select
...
- 6,641
601
votes
13
answers
304k
views
How do you rename a MongoDB database?
There's a typo in my MongoDB database name and I'm looking to rename the database.
I can copy and delete like so...
db.copyDatabase('old_name', 'new_name');
use old_name
db.dropDatabase();
Is there ...
- 9,024
582
votes
10
answers
630k
views
How to shrink/purge ibdata1 file in MySQL
I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit ...
- 24.4k
570
votes
25
answers
831k
views
Kill a postgresql session/connection
How can I kill all my postgresql connections?
I'm trying a rake db:drop but I get:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the ...
- 18.1k
566
votes
40
answers
128k
views
Database development mistakes made by application developers [closed]
What are common database development mistakes made by application developers?
563
votes
17
answers
211k
views
Room - Schema export directory is not provided to the annotation processor so we cannot export the schema
I am using Android Database Component Room
I've configured everything, but when I compile, Android Studio gives me this warning:
Schema export directory is not provided to the annotation processor ...
- 12.8k
562
votes
36
answers
1.5m
views
Restore a postgres backup file using the command line?
Locally, I use pgadmin3. On the remote server, however, I have no such luxury.
I've already created the backup of the database and copied it over, but is there a way to restore a backup from the ...
- 10.5k
552
votes
6
answers
453k
views
MySQL: @variable vs. variable. What's the difference?
In another question I posted someone told me that there is a difference between:
@variable
and:
variable
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can ...
- 36.9k
550
votes
26
answers
984k
views
How should I resolve --secure-file-priv in MySQL?
I am learning MySQL and tried using a LOAD DATA clause. When I used it as below:
LOAD DATA INFILE "text.txt" INTO table mytable;
I got the following error:
The MySQL server is running with the --...
- 9,304
545
votes
30
answers
840k
views
How can I stop redis-server?
I apparently have a redis-server instance running because when I try to start a new server by entering redis-server, I'm greeted with the following:
Opening port: bind: Address already in use
I can'...
- 18.9k
540
votes
8
answers
656k
views
Maximum length for MySQL type text
I'm creating a form for sending private messages and want to set the maxlength value of a textarea appropriate to the max length of a text field in my MySQL database table. How many characters can a ...
- 22.4k
538
votes
8
answers
434k
views
Best way to store password in database [closed]
I am working on a project that has to have authentication (username and password)
It also connects to a database, so I figured I would store the username and password there. However, it seems like ...
- 11.6k
518
votes
20
answers
623k
views
Show Procedures/Functions MySQL Command Line
How can I see the list of the stored procedures or stored functions in mysql command line like SHOW TABLES; or SHOW DATABASES; commands.
- 15.4k
508
votes
5
answers
112k
views
What's the Hi/Lo algorithm?
What's the Hi/Lo algorithm?
I've found this in the NHibernate documentation (it's one method to generate unique keys, section 5.1.4.2), but I haven't found a good explanation of how it works.
I know ...
- 5,083