All Questions
848 questions
129
votes
3
answers
66k
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,741
49
votes
2
answers
57k
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
142
votes
5
answers
255k
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/...
- 70.1k
64
votes
6
answers
87k
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,481
39
votes
2
answers
66k
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
138
votes
5
answers
169k
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,655
212
votes
7
answers
308k
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,299
46
votes
1
answer
18k
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 ...
- 713
89
votes
4
answers
51k
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 ...
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.
...
- 62.4k
29
votes
4
answers
7k
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:...
- 98.7k
67
votes
3
answers
89k
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 ...
- 771
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
25
votes
3
answers
14k
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
26
votes
2
answers
10k
views
How do I properly perform a MySQL bake-off?
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
- 3,934
16
votes
3
answers
19k
views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
- 1,271
16
votes
2
answers
9k
views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
- 600
78
votes
6
answers
153k
views
How do I efficiently get "the most recent corresponding row"?
I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
- 1,619
34
votes
3
answers
31k
views
Is the CPU performance relevant for a database server?
This is a purely theoretical question. Let's say I have an application deployed on multiple servers.
A load balancer,
Multiple/scalable applications servers
A (single) database server (for the ...
- 451
70
votes
3
answers
119k
views
Optimizing a Postgres query with a large IN
This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
- 1,043
24
votes
3
answers
11k
views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
- 405
6
votes
3
answers
10k
views
Does a query with a primary key and foreign keys run faster than a query with just primary keys?
SELECT something FROM table WHERE primary_key = ?
vs.
SELECT something FROM table WHERE primary_key = ? AND other_key = ?
Say that this is a scenario where the inclusion of other_key does NOT ...
- 429
60
votes
6
answers
33k
views
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
- 4,903
54
votes
2
answers
88k
views
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
- 661
51
votes
5
answers
56k
views
Aggressive Autovacuum on PostgreSQL
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
...
- 783
9
votes
3
answers
15k
views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
- 1,425
7
votes
3
answers
2k
views
Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N
We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.
The problem generally ...
- 185
93
votes
4
answers
98k
views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
Edit: Not a dupe of:
...
- 1,275
38
votes
3
answers
9k
views
Why does SQL Server use a better execution plan when I inline the variable?
I have a SQL query that I am trying to optimize:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @...
- 820
29
votes
3
answers
6k
views
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.
Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys ...
- 70.5k
5
votes
2
answers
9k
views
MySQL on SSD - what are the disadvantages? [closed]
Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost.
Given that 20% of all queries in my database are INSERTs and ...
- 211
131
votes
7
answers
156k
views
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 ...
- 1,413
71
votes
5
answers
138k
views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
- 855
21
votes
3
answers
9k
views
Why is an OR statement slower than UNION?
Database version: PostgreSQL 12.6
I have a table with 600,000 records.
The table has the columns:
name (varchar)
location_type (int) enum values: (1,2,3)
ancestry (varchar)
Indexes:
ancestry (btree)...
- 321
19
votes
1
answer
6k
views
SQL Server--If logic in stored procedure and the plan cache
SQL Server 2012 and 2016 Standard:
If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
- 315
14
votes
1
answer
8k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243
6
votes
2
answers
2k
views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN (...
- 239
14
votes
1
answer
3k
views
Can declaring function volatility IMMUTABLE harm performance?
Postgres functions are declared with volatility classification VOLATILE, STABLE or IMMUTABLE. The project is known to be very strict with these labels for built-in functions. And with good reason. ...
- 182k
7
votes
3
answers
17k
views
Why does my query run fast in Environment A, but slow in Environment B?
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or ...
6
votes
2
answers
14k
views
Local database vs Amazon RDS
I am doing large INSERT into a local MySQL database
I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable
I expect this database to ...
- 163
54
votes
1
answer
59k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 643
28
votes
5
answers
90k
views
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
- 1,956
25
votes
2
answers
16k
views
Get query plan for SQL statement nested in a PL/pgSQL function
Is it possible to get the query plan for an SQL statement executed inside a user defined function (UDF)? Like with using EXPLAIN in pgAdmin or psql as client. I see the UDF abstracted away into a ...
- 427
39
votes
5
answers
86k
views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
- 1,895
32
votes
3
answers
21k
views
How Does Table Partitioning Help?
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
- 1,271
18
votes
3
answers
8k
views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select count(*)...
- 283
15
votes
3
answers
5k
views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
- 2,052
8
votes
1
answer
1k
views
Does MySQL still handle indexes in this way?
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.
If a ...
- 869
7
votes
1
answer
6k
views
Unused index in range of dates query
I have a query that is not using existing indices and I do not understand why.
The table:
mustang=# \d+ bss.amplifier_saturation
Table "bss....
- 173
4
votes
3
answers
7k
views
MySQL slow log configuration
Is there a way of making MySQL slow logs to start new log file every day ?
At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...
- 741