"Ed" <[EMAIL PROTECTED]> wrote:
> 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.
Show structures of your table, output of EXPLAIN for each SELECT.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
