All Questions
9,016
questions
297
votes
4
answers
344k
views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the transaction log ...
- 18.1k
122
votes
3
answers
57k
views
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?
...
- 1,651
49
votes
2
answers
52k
views
Configuring PostgreSQL for read performance
Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.
The primary key (constraint) structure is similar ...
- 593
280
votes
10
answers
284k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,029
142
votes
5
answers
248k
views
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/...
- 69.6k
34
votes
4
answers
22k
views
How to move a database from SQL Server 2012 to SQL Server 2005
What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?
I know I cannot:
restore a backup of the database on SQL Server 2005
detach & attach
I ...
- 1,005
75
votes
8
answers
67k
views
MAXDOP setting algorithm for SQL Server
When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting:
/*
This will recommend a MAXDOP setting appropriate for your machine's NUMA ...
- 69.5k
63
votes
6
answers
85k
views
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an ...
- 1,451
39
votes
2
answers
65k
views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
- 1,271
143
votes
8
answers
228k
views
Pattern matching with LIKE, SIMILAR TO or regular expressions
I had to write a simple query where I go looking for people's name that start with a B or a D:
SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1
I was wondering if ...
- 1,775
64
votes
1
answer
23k
views
What event information can I get by default from SQL Server?
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its ...
- 179k
136
votes
5
answers
150k
views
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 ...
- 1,621
23
votes
1
answer
55k
views
Upgrade SQL Server 2000 database to 2008 R2 and enable new features
I have recently upgraded SQL Server 2000 database to 2008 R2.
What I did was:
Shutdown SQL Server 2000 (express) service on old machine,
Move datafiles (mydatabase.mdf and mydatabase.ldf) to new ...
- 345
103
votes
6
answers
77k
views
Retrieving n rows per group
I often need to select a number of rows from each group in a result set.
For example, I might want to list the 'n' highest or lowest recent order values per customer.
In more complex cases, the ...
- 82.1k
207
votes
7
answers
291k
views
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 ...
- 4,229
117
votes
5
answers
231k
views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
- 23.3k
31
votes
3
answers
7k
views
What is a deterministic method for evaluating a sensible buffer pool size?
I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory (...
- 69.5k
65
votes
3
answers
81k
views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
- 3,122
37
votes
4
answers
18k
views
I Need to Shrink My Database - I just freed a lot of space
This question is asked in various forms here but the question boils down to:
I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.
How can I ...
- 18.1k
86
votes
4
answers
47k
views
Why is my query suddenly slower than it was yesterday?
[Salutations]
(check one)
[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,
I have a (check all that apply)
[ ] query [ ] stored procedure [ ] database thing maybe
that ...
- 38.2k
42
votes
1
answer
16k
views
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:
922475bb-ad93-43ee-9487-d2671b886479
There ...
- 663
37
votes
5
answers
170k
views
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient....
- 1,555
29
votes
4
answers
17k
views
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then ...
- 4,229
15
votes
3
answers
8k
views
Problem with MySQL subquery
Why does this query
DELETE FROM test
WHERE id = ( SELECT id
FROM (SELECT * FROM test) temp
ORDER BY RAND()
LIMIT 1
);
sometimes delete 1 row, ...
- 253
144
votes
4
answers
159k
views
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
- 8,341
46
votes
1
answer
31k
views
SQL injection in Postgres functions vs prepared queries
In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?
27
votes
3
answers
14k
views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
- 881
141
votes
8
answers
72k
views
Why shouldn't we allow NULLs?
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.
All I can seem to ...
- 42k
53
votes
3
answers
86k
views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
user755
29
votes
4
answers
6k
views
Can spatial index help a "range - order by - limit" query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
- 96.8k
253
votes
6
answers
440k
views
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 ...
- 2,934
139
votes
1
answer
159k
views
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 ...
- 1,555
41
votes
3
answers
10k
views
difference in execution plans on UAT and PROD server
I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs).
Both UAT and PROD are having exactly data and indexes.
...
- 62k
81
votes
4
answers
207k
views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
- 1,033
55
votes
3
answers
72k
views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
- 4,479
40
votes
2
answers
41k
views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about one ...
- 701
13
votes
2
answers
43k
views
Return counts for multiple ranges in a single SELECT statement
I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3,...
- 397
13
votes
2
answers
11k
views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column.
Consider the following table:
lap_id (serial), lap_no (int), car_type (...
- 289
482
votes
2
answers
254k
views
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
- 83k
47
votes
5
answers
71k
views
When is it OK to shrink a Database?
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
- 947
10
votes
1
answer
8k
views
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
my.cnf (5.5 and 5.1 is the same) :
back_log=200
max_connections=512
max_connect_errors=999999
key_buffer=512M
max_allowed_packet=8M
table_cache=512
sort_buffer=8M
read_buffer_size=8M
thread_cache=8
...
- 251
166
votes
12
answers
111k
views
Should binary files be stored in the database?
What is the best place for storing binary files that are related to data in your database? Should you:
Store in the database with a blob
Store on the filesystem with a link in the database
Store in ...
- 39.4k
119
votes
4
answers
21k
views
Why are numbers tables "invaluable"?
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
...
- 2,364
74
votes
5
answers
14k
views
Is there a name for this database schema of key values?
We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one ...
- 853
68
votes
3
answers
85k
views
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
- 781
40
votes
6
answers
70k
views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
- 1,377
70
votes
4
answers
54k
views
Function Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I ...
- 23.3k
39
votes
3
answers
41k
views
Are regular VACUUM ANALYZE still recommended under 9.1?
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
I have a largish database (...
- 1,413
25
votes
3
answers
13k
views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
- 353
11
votes
2
answers
7k
views
Will these two queries result in a deadlock if executed in sequence?
This is almost certainly the cause of my other question, but I thought it was worth separating the two as I have a hypothesis based on the following log that I would love to have falsified or verified....
- 600