index merge union not applied
Hi,
I have a index problem, it seems to me that an index merge union should occure.
here is the data:
2 tables: object and object_link
CREATE TABLE object (
idobject int(10) unsigned NOT NULL auto_increment,
idobject_type int(10) unsigned NOT NULL,
idobject_parent int(10) unsigned NOT NULL,
`code` varchar(255) default NULL,
PRIMARY KEY (idobject),
KEY object_index_code (`code`),
KEY object_index_idobject_parent (idobject_parent),
KEY idobject_type (idobject_type),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE object_link (
idobject int(10) unsigned NOT NULL,
idobject_linked int(10) unsigned NOT NULL,
idobject_link_type int(10) unsigned NOT NULL,
number int(11) NOT NULL default '1',
PRIMARY KEY (idobject,idobject_linked,idobject_link_type),
KEY idobject_link (idobject_linked,idobject_link_type),
KEY idobject_target (idobject,idobject_link_type),
KEY idobject_linked (idobject_linked)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
query :
SELECT
t1.*,
t2.*
FROM
object AS t1
LEFT JOIN object_link AS OL2
ON (OL2.idobject = t1.idobject AND OL2.idobject_link_type = 13 )
LEFT JOIN object AS t2
ON (t2.idobject_parent = t1.idobject OR t2.idobject = OL2.idobject_linked )
WHERE
t1.idobject_parent = 3371
it works but is slow.
here is the EXPLAIN :
+----+-------------+-------+------+--------------------------------------+------------------------------+---------+-------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------+------------------------------+---------+-------------+-------+-------------+
| 1 | SIMPLE | t1 | ref | object_index_idobject_parent | object_index_idobject_parent | 4 | const | 20 | |
| 1 | SIMPLE | OL2 | ref | PRIMARY,idobject_target | PRIMARY | 4 | t1.idobject | 1 | Using index |
| 1 | SIMPLE | t2 | ALL | PRIMARY,object_index_idobject_parent | NULL | NULL | NULL | 89059 | |
+----+-------------+-------+------+--------------------------------------+------------------------------+---------+-------------+-------+-------------+
I can't figure out why it doesn't use a index merge for the third one.
I'm using mysql 5.0.45
Any help would be appreciated.
Edited 1 time(s). Last edit at 04/15/2008 09:42AM by Pernot Christian.