>>>>> "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
