The query optimizer is the component of a database management system that attempts to determine the most efficient way to execute a query.

learn more… | top users | synonyms

4
votes
1answer
2k 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 ...
28
votes
3answers
2k 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 ) ...
-4
votes
1answer
561 views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
12
votes
2answers
3k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
1
vote
1answer
2k views

MySQL optimization

I'm trying to optimize a MySQL Server to be able to serve as many connections as it can. The server is in AmazonAWS RDS and has currently the following resources: --7.5 GB memory, 4 ECUs (2 ...
9
votes
3answers
780 views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
6
votes
1answer
1k 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 ...
4
votes
2answers
1k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
3
votes
1answer
80 views

PostgreSQL + query planner + amount of entities

I'm working on college work about how query planner uses statistics and makes the most optimal query. I have read the 57.1. Row Estimation Examples article and I know how's the PostgreSQL calculates ...
2
votes
1answer
152 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...