Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
142
votes
5 answers

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help Setup The servers are quite hefty with an OLAP/DataWarehouse (DW) type load: Primary: 96GB RAM, 8…
gbn
  • 69,809
  • 8
  • 163
  • 243
142
votes
7 answers

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX…
Majid Azimi
  • 2,261
  • 3
  • 23
  • 24
140
votes
1 answer

Postgres UPDATE ... LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular. I want a…
vastlysuperiorman
  • 1,565
  • 2
  • 10
  • 8
138
votes
8 answers

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the…
Christoph
  • 1,573
  • 2
  • 11
  • 8
137
votes
5 answers

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor. I need all of the fields (or nearly…
Joe
  • 1,635
  • 2
  • 11
  • 13
135
votes
3 answers

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
Basil Bourque
  • 10,806
  • 20
  • 59
  • 92
133
votes
2 answers

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL: This…
Jonas
  • 32,975
  • 27
  • 61
  • 64
130
votes
5 answers

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. There is a table with around 300 million rows, it's…
SBhojani
  • 1,403
  • 3
  • 10
  • 4
130
votes
7 answers

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that…
misterjaytee
  • 1,403
  • 3
  • 12
  • 8
127
votes
4 answers

Difference between Sharding And Replication on MongoDB

I am just confused about how Sharding and Replication work. According to the definitions I found in the documentation: Replication: A replica set in MongoDB is a group of mongod processes that maintain the same data set. Sharding: Sharding is a…
Saad Saadi
  • 1,401
  • 2
  • 10
  • 7
127
votes
10 answers

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?
Adam Matan
  • 11,659
  • 29
  • 80
  • 95
126
votes
5 answers

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ON A.idx = B.idx WHERE B.idx IS NULL SELECT…
Michael Richardson
  • 1,435
  • 2
  • 10
  • 9
124
votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? Additionally, I created an index on A, but Postgres still…
Luciano
  • 1,671
  • 3
  • 12
  • 8
121
votes
1 answer

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table inner join. I did not make this query but the developer…
jackhammer013
  • 1,409
  • 2
  • 12
  • 11
120
votes
11 answers

When to use NULL and when to use an empty string?

I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general: Is there a logical scenario in which it would be useful to distinguish an empty string from NULL? What would be the physical storage implications for…
Maniero
  • 2,728
  • 6
  • 28
  • 29
1 2 3
…
99 100