List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 8 1999 1:06pm
Subject:Optimiser bug in 3.22.20a?
View as plain text  
>>>>> "tony" == tony  <tony@stripped> writes:

>> Description: The optimiser doesn't seem to join these 2 tables properly
>> How-To-Repeat:

tony> I have two tables:

tony>   CREATE TABLE t_items (
tony>     uniqueid bigint(20) unsigned zerofill DEFAULT '00000000000000000000' NOT NULL
> auto_increment,
tony>     itemid varchar(13) DEFAULT '' NOT NULL,
tony>     PRIMARY KEY (uniqueid),
tony>     KEY itemid (itemid)
tony>   );

tony>   CREATE TABLE t_xref (
tony>     itemid varchar(13) DEFAULT '0' NOT NULL,
tony>     realid varchar(13) DEFAULT '0' NOT NULL,
tony>     PRIMARY KEY (itemid),
tony>     KEY (realid)
tony>   );

tony> Items that have an entry in t_items, may or may not have a corresponding entry in
> t_xref.

tony> t_items has approx 87,500 entries, and t_xref has approx 13,000

tony> Now, I want to get a list of uniqueids from t_items for a particular realid.

tony> As t_xref is keyed on realid, and matches exactly a key from t_items, I would
> have thought
tony> this would be very fast.

tony> However:

tony>     SELECT i.uniqueid
tony>       FROM t_xref x, t_items i
tony>      WHERE i.itemid = x.itemid
tony>        AND x.realid = 7000000019429

tony> takes over 2 seconds to run.

tony> An explain gives:

tony>
> +-------+--------+----------------+---------+---------+----------+-------+------------+
tony> | table | type   | possible_keys  | key     | key_len | ref      | rows  | Extra 
>     |
tony>
> +-------+--------+----------------+---------+---------+----------+-------+------------+
tony> | i     | ALL    | itemid         | NULL    |    NULL | NULL     | 87519 |       
>     |
tony> | x     | eq_ref | PRIMARY,realid | PRIMARY |      13 | i.itemid |     1 | where
> used |
tony>
> +-------+--------+----------------+---------+---------+----------+-------+------------+


tony> If, however I force a straight join

tony>     SELECT i.uniqueid
tony>       FROM t_xref x STRAIGHT_JOIN t_items i
tony>      WHERE i.itemid = x.itemid
tony>        AND x.realid = 7000000019429

tony> the query only takes about 0.05 seconds.

What is the explain for the later query?

>> Suggested fix: 

Try running 'isamchk -a' on your tables.  Do 'mysqladmin flush-tables' 
and try again!

You can also try doing:

SELECT i.uniqueid
  FROM t_xref x, t_items i
  WHERE i.itemid = x.itemid
 AND x.realid = '7000000019429'

(Why are the columns VARCHAR and not bigint ?)

Regards,
Monty
Thread
Optimiser bug in 3.22.20a?tony7 Aug
  • Optimiser bug in 3.22.20a?Michael Widenius8 Aug
    • Re: Optimiser bug in 3.22.20a?tony8 Aug
      • Re: Optimiser bug in 3.22.20a?Benjamin Pflugmann8 Aug
        • Re: Optimiser bug in 3.22.20a?Michael Widenius8 Aug