Hi!
>>>>> "nsabbi" == nsabbi <[EMAIL PROTECTED]> writes:
nsabbi> Hi, I have the following tables :
<cut>
nsabbi> explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
nsabbi> k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
nsabbi> count(k.keyword)=1 limit 1, 26;
nsabbi>
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
nsabbi> | table | type | possible_keys | key | key_len | ref | rows |
nsabbi> Extra |
nsabbi>
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
nsabbi> | k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 |
nsabbi> where used; Using index; Using temporary |
nsabbi> | a | eq_ref | PRIMARY | PRIMARY | 16 | k.codice | 1 |
nsabbi> |
nsabbi>
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
nsabbi> 2 rows in set (4.21 sec)
nsabbi> If I add an index:
<cut>
nsabbi> explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
nsabbi> k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
nsabbi> count(k.keyword)=1 limit 1, 26;
nsabbi>
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
nsabbi> | table | type | possible_keys | key | key_len | ref | rows |
nsabbi> Extra |
nsabbi>
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
nsabbi> | a | ALL | PRIMARY | NULL | NULL | NULL | 412345 |
nsabbi> Using temporary |
nsabbi> | k | ref | PRIMARY,codice | codice | 16 | a.codice | 1 |
nsabbi> where used; Using index |
nsabbi>
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
nsabbi> 2 rows in set (0.00 sec)
<cut>
>From the optimizer point of few, it does the right thing because the
number of examined row combinations goes down from 437735 to 412345.
It's probably the rows-in-range estimator in InnoDB that needs some
more tuning.
What is the output from the following queries:
shows index from keywords;
shows index from articoli;
select count(*) from keywords where keyword IN ('tubo');
select count(distinct codice),count(*) from keywords;
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
