Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
213
questions
145
votes
4
answers
161k
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,501
69
votes
3
answers
106k
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,023
4
votes
2
answers
5k
views
Can Postgres use an index-only scan for this query with joined tables?
This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns?
Consider the inverse of the schema in the other question where you filter in the joined-on table:
CREATE TABLE ...
- 669
6
votes
3
answers
9k
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 ...
- 419
129
votes
7
answers
150k
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,393
6
votes
2
answers
1k
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
8
votes
1
answer
691
views
Optimizer not choosing index union plan
Why is the following query slow?
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
...
- 2,187
18
votes
2
answers
41k
views
Do covering indexes in PostgreSQL help JOIN columns?
I have a whole lot of tables that look vaguely like this:
CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
- 669
6
votes
1
answer
32k
views
Maximum possible memory usage: 16.2G (874% of installed RAM)
Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB?
# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug ...
- 183
5
votes
2
answers
12k
views
Scheduled optimization of tables in MySQL InnoDB
What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
- 73
39
votes
5
answers
157k
views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
- 6,702
4
votes
1
answer
7k
views
Mysql Memory table getting many locks
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.
This table has the following activity:
1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of ...
- 205
26
votes
2
answers
37k
views
Slow index scans in large table
Update 2020-08-04:
Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are ...
- 429
21
votes
2
answers
41k
views
Postgres JOIN conditions vs WHERE conditions
Postgres newbie here.
I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
- 407
16
votes
4
answers
2k
views
Is a join optimized to a where clause at runtime?
When I write a query like this...
select *
from table1 t1
join table2 t2
on t1.id = t2.id
Does the SQL optimizer, not sure if that is the correct term, translate that to...
select *
from table1 t1, ...
- 263
16
votes
3
answers
36k
views
Why does MySQL ignore the index even on force for this order by?
I run an EXPLAIN:
mysql> explain select last_name from employees order by last_name;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+
| ...
- 1,003
8
votes
2
answers
4k
views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
- 183
4
votes
3
answers
7k
views
Optimize a query on two big tables
I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
12
votes
1
answer
13k
views
Type conversion in expression may affect "CardinalityEstimate" in query plan choice?
I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.
We constraint the events on ...
- 2,318
1
vote
2
answers
369
views
What strategy does MySQL optimizer use when choosing between two indexes?
In question:
Optimize a query with two range conditions
the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN, and that was the ...
23
votes
2
answers
2k
views
Queries without Good Enough Plan Found
I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are:
What are all ...
- 899
20
votes
3
answers
18k
views
Trace Flag 4199 - Enable globally?
This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
- 681
19
votes
4
answers
5k
views
SQL Server cardinality hint
Is there a way how to 'inject' a cardinality estimation to a SQL Server optimizer (any version)?
i.e. something similar to Oracle's cardinality hint.
My motivation is driven by the article, How ...
- 253
13
votes
1
answer
3k
views
How does SQL Server's optimizer estimate the number of rows in a joined table?
I am running this query in the AdventureWorks2012 database:
SELECT
s.SalesOrderID,
d.CarrierTrackingNumber,
d.ProductID,
d.OrderQty
FROM Sales.SalesOrderHeader s
JOIN Sales....
- 2,619
7
votes
1
answer
534
views
Why is PostgreSQL 9.5 not using my newest index for ORDER BY, even though it uses similar indices just fine?
(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?)
PostgreSQL 9.5.
I can't divulge full details, but table has 22 columns and 5 indexes:...
- 424
6
votes
1
answer
2k
views
Stored procedure with recompile
I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params ...
- 1,381
6
votes
1
answer
4k
views
Type conversion in expression may affect “CardinalityEstimate” - on a computed column?
I had a look at this question:
Type conversion in expression may affect “CardinalityEstimate” in query plan choice?
But that was related to collation and not with computed columns.
I have the ...
- 15.8k
5
votes
4
answers
4k
views
MySQL Query Optimization : Indexing and Pagination
I'm adding pagination to my 'latest news' php script and running in to an issue.
SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit
I ...
Dave
5
votes
1
answer
6k
views
PostgreSQL FTS and Trigram-similarity Query Optimization
I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
- 161
3
votes
1
answer
2k
views
MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
I have a mysql insert on update query like so
insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';
col1, col2 ...
3
votes
2
answers
2k
views
updating statistics using STATS_STREAM or WITH FULLSCAN?
the syntax for creating statistics is:
-- Syntax for SQL Server and Azure SQL Database
UPDATE STATISTICS table_or_indexed_view_name
[
{
{ index_or_statistics__name ...
- 15.8k
2
votes
2
answers
2k
views
Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]
I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT:
SELECT
customs.id AS custom_id, customs.custom_name AS ...
40
votes
3
answers
5k
views
Unexpected scans during delete operation using WHERE IN
I've got a query like the following:
DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
...
- 1,465
16
votes
2
answers
16k
views
How to optimise T-SQL query using Execution Plan
I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
- 435
14
votes
2
answers
13k
views
Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?
In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
- 545
13
votes
2
answers
2k
views
The use of NOT logic in relation to indexes
According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:
Neither leading wildcard characters not NOT logic allow the query optimizer to use ...
- 4,522
9
votes
2
answers
8k
views
Optimizing queries on a range of timestamps (one column)
I am using Postgres 9.3 through Heroku.
I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
- 1,163
8
votes
1
answer
4k
views
Using FORCE INDEX
I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O.
It was something to do with the MySQL Query Optimizer not needing to go ...
- 1,017
6
votes
2
answers
10k
views
Can I optimize this MERGE statement?
I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows.
In my development environment, the query ...
- 193
5
votes
1
answer
13k
views
Why isn't oracle using an index for distinct query ?
This is the scenario
SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true)
PL/SQL procedure successfully completed.
SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
- 376
5
votes
2
answers
2k
views
Wordcount in a field (all and unique) - is there a more elegant/optimal way?
Answering this question,
Given this table (constructed from the question):
CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);
INSERT INTO wordcount (description) ...
- 29.5k
4
votes
4
answers
1k
views
Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR
I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:
SELECT *
FROM Users u
LEFT JOIN Comments c
ON u.Id = c....
- 1,908
3
votes
1
answer
842
views
How can I find out if a SQL function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
- 62.1k
3
votes
2
answers
12k
views
How can I defragment tables in MySQL?
I have a database named COUNTY. COUNTY has a fragmented table GEO.
I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the error
"Table does not support optimize, doing ...
- 55
2
votes
1
answer
1k
views
OFFSET and LIMIT on complex query
I am doing a pretty complex query in Postgres 9.3.4:
SELECT p.*
FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
, 17804, 20717, 27598, 27599}'::int[]) s(source_id)...
- 239
92
votes
4
answers
83k
views
Are views harmful for performance in PostgreSQL?
The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):
The danger with using views is filtering a query against a view,
expecting to read a very ...
- 1,399
25
votes
1
answer
4k
views
Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?
I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
- 55.6k
22
votes
2
answers
35k
views
Does an empty column value occupy same storage space as a filled column value?
I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
- 353
16
votes
4
answers
9k
views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
- 1,677
15
votes
3
answers
4k
views
Why is my EXISTS query doing an index scan instead of an index seek?
I am working on optimizing some queries.
For the query below,
SET STATISTICS IO ON;
DECLARE @OrderStartDate DATETIME2 = '27 feb 2016';
DECLARE @OrderEndDate DATETIME2 = '28 feb 2016';
SELECT o....
- 15.8k