MySQL Forums
Forum List  »  Quality Assurance

index merge union not applied
Posted by: Pernot Christian
Date: April 15, 2008 09:40AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
index merge union not applied
3297
April 15, 2008 09:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.