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

Reply via email to