Optimizer Penalty for using LIKE + ORDER BY + LIMIT ?

[Ed]

------=_NextPart_000_001D_01C34FCC.1D2B5E50
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.

=20

Please see below:

=20

Optimizer Penalty for using LIKE + ORDER BY + LIMIT

=20

a) Using Full Text Search

mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id =3D =
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)

=20

b) Using LIKE -- a 2.30 seconds penalty

mysql> SELECT a.dvd_id FROM dvd a, dvd_search s WHERE a.dvd_id =3D =
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)

=20

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 =3D =
s.dvd_id
and s.title like '% breakfast%' ORDER BY a.title_sort, a.director;

10 rows in set (0.10 sec)

=20

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 =3D =
s.dvd_id
and s.title like '% breakfast%' LIMIT 0,50;

10 rows in set (0.10 sec)

=20

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)

=20

Each table has about 32,000 rows and they are 260MB and 10MB in size
(data+index). The machine has 1GB of memory.

=20

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.

=20

Any help will be greatly appreciated.

=20

Thanks,

=20

Ed
------=_NextPart_000_001D_01C34FCC.1D2B5E50--