Related Documentation Download this Manual
PDF (US Ltr) - 37.7Mb
PDF (A4) - 37.7Mb
PDF (RPM) - 32.8Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 133.0Kb
Man Pages (Zip) - 189.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

where-optimizations

This page has moved or been replaced. The new page is located here:

http://dev.mysql.com/doc/refman/8.0/en/where-optimization.html

Please update any bookmarks that point to the old page.


User Comments
  Posted by André Somplatzki on April 15, 2005
Indices lose their speed advantage when using them in OR-situations (4.1.10):

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

is much faster than

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';
  Posted by Duncan Simpson on April 19, 2005
The query optimiser also does badly on examples like

SELECT id FROM foo WHERE bar IN (SELECT bar FROM baz WHERE qux='foo')

where foo is a large table and baz a small one. Doing the subselect first would allow the use on an index to eliminate most of foo, which is what happens if you say

SELECT foo.id FROM foo, baz WHERE foo.bar=baz.bar and baz.qux='foo'

However the latter move is not possible if the first operation is DELETE and you want to avoid MySQL specific syntax (DELETE FROM .. USING .. WHERE ... also does the sensible thing but is not SQL-99).
  Posted by Henrik Grubbström on March 24, 2006
Indexes are ignored for the <> operator:

SELECT * FROM tab WHERE score <> 0;

This can be a problem if the table is very slanted (eg >99% of of the rows have the value that is filtered). The obvious workaround is to use a UNION:

(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);

  Posted by Gints Plivna on September 19, 2008
SELECT * FROM tab WHERE score <> 0;
functionally IS NOT THE SAME AS
(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);
because UNION filters out duplicates.
If one needs this one has to use UNION ALL.
  Posted by Pól Ua Laoínecháin on June 9, 2014
I think that Henrik implicitly assumed that the table (tab) has a primary key (and that score is not part of it), in which case he's correct.
  Posted by Tim Yan on May 10, 2015
Sometime this statement is not true:

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

is much faster than

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';

If you have many records with 'foo' or 'baar', to satisfy your union, mysql has to pull all 'foo' and 'baar' records and do the 'union' merge. If you have a 5 million records with 3 million 'foo' and 2 'baar', your query will be very slow, much worse than the OR statement.

When your table is very flat (many columns), the problem can be worse since the temp table used for the "union" can be huge.

It can also be an issue when you try to pull "first 100" records using "limit". MySQL has to pull all records before it can do the UNION then pick the first 100 records for you. A table scan can actually stop earlier when MySQL finds enough result.

Most people will consider it a rare case. Think about a search on Ip addresses in your web log table, you may find surprisingly how many records matching one Ip address (or range).


Also, the Union query may generate different results. You need UNION ALL and this:

SELECT * FROM a WHERE index1 = 'foo'
UNION ALL
SELECT * FROM a WHERE index2 = 'baar' and index1 !='foo'

Sign Up Login You must be logged in to post a comment.