There seems to be a significant penalty imposed by the optimizer when these
3 clauses are uses together. If we use the Full Text Search the penalty is
gone. However, the Full Text Search is not a very good option in my case as
it does not offer the granularity needed.
Please see below:
Optimizer Penalty for using LIKE + ORDER BY + LIMIT
a) Using Full Text Search
mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id = s.dvd_id
and match (s.title) against ('breakfast' in boolean mode) ORDER BY
a.title_sort, a.director LIMIT 0,50;
10 rows in set (0.00 sec)
b) Using LIKE -- a 2.30 seconds penalty
mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id = s.dvd_id
and s.title like '% breakfast%' ORDER BY a.title_sort, a.director LIMIT
0,50;
10 rows in set (2.30 sec)
c) Using LIKE, but removing the LIMIT - the penalty is gone
mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id = s.dvd_id
and s.title like '% breakfast%' ORDER BY a.title_sort, a.director;
10 rows in set (0.10 sec)
d) Using LIKE, but removing the ORDER BY - the penalty is gone
mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id = s.dvd_id
and s.title like '% breakfast%' LIMIT 0,50;
10 rows in set (0.10 sec)
e) Even when eliminating the second table from the query the penalty still
remains
mysql> SELECT a.dvd_id FROM dvd a WHERE a.title like '%breakfast%' ORDER BY
a.title_sort, a.director LIMIT 0,50;
10 rows in set (0.58 sec)
Each table has about 32,000 rows and they are 260MB and 10MB in size
(data+index). The machine has 1GB of memory.
I'm using 4.0.12. Unfortunately I'm unable to verify if this has been
corrected in 4.1.0 because of the SSL library compatibility -- I presume you
are aware of the libcrypto.so.0.9.6 and libssl.so.0.9.6 issue.
Any help will be greatly appreciated.
Thanks,
Ed
