Most Popular
1500 questions
260
votes
6 answers
How large should be mysql innodb_buffer_pool_size?
I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind the server to halt. The average query per second…
alfish
- 3,004
- 7
- 21
- 18
258
votes
6 answers
Force drop db while others may be connected
I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB.
How can I implement it?
I'm using dropdb currently, but…
Alex
- 2,775
- 2
- 18
- 7
244
votes
18 answers
How do you document your databases?
I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.
How do you document your database? (SQL-Server)…
user316
- 951
- 3
- 7
- 4
237
votes
3 answers
Granting a user account permission to create databases in PostgreSQL
How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
GSto
- 2,473
- 2
- 15
- 6
221
votes
2 answers
Create a MySQL database with charset UTF-8
I'm new to MySQL and I would like to know:
How can I create a database with charset utf-8 like I did in navicat?
create mydatabase;
...seems to be using some kind of default charset.
user3397998
- 2,321
- 2
- 13
- 4
219
votes
6 answers
How to get the name of the current database from within PostgreSQL?
Using \c in PostgreSQL will connect to the named database.
How can the name of the current database be determined?
Entering:
my_db> current_database();
produces:
ERROR: syntax error at or near "current_database"
LINE 1:…
Amelio Vazquez-Reina
- 2,315
- 2
- 15
- 8
216
votes
5 answers
Granting access to all tables for a user
I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database. I must be…
PlaidFan
- 2,265
- 2
- 14
- 7
215
votes
7 answers
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly.
Due to my comcast…
Patrick
- 4,329
- 7
- 29
- 28
213
votes
15 answers
SQL: SELECT All columns except some
Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table.
Something like:
SELECT * -the_geom FROM segments;
I once heard that this…
Adam Matan
- 12,079
- 30
- 82
- 96
209
votes
17 answers
How do I find PostgreSQL's data directory?
I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory.
I'm on MacOsX if that helps.
/usr/local/postgres…
randomguy
- 2,231
- 2
- 14
- 6
208
votes
6 answers
When should a primary key be declared non-clustered?
While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED
When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key?
Thanks in advance
Stuart Blackler
- 4,540
- 7
- 30
- 43
192
votes
7 answers
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, Column3
FROM SomeTable
)
SELECT * FROM…
Rachel
- 8,547
- 20
- 51
- 74
188
votes
6 answers
Good explanation of cascade (ON DELETE/UPDATE) behavior
I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.
For example, if I have two tables -…
Johntron
- 2,013
- 2
- 13
- 7
178
votes
20 answers
Should developers be able to query production databases?
Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production…
Tom Hunter
- 2,179
- 3
- 16
- 11
177
votes
6 answers
Guid vs INT - Which is better as a primary key?
I've being reading around reasons to use or not Guid and int.
int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and…
BrunoLM
- 3,533
- 7
- 28
- 22