Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.6Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.2Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
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
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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.