MySQL Forums
Forum List  »  Optimizer & Parser

Problem with index and left join
Posted by: Erland Isaksson
Date: May 05, 2006 03:04PM

I have a problem with getting a index to work in MySQL 4.1 or later. It works perfectly in 4.0.24 but it does not work in 4.1 and later releases, I have verified it in 4.0.24(which works) and in 4.1.16, 4.1.19, 5.0.21.

I'm running Windows XP and mysqld-nt with default installation.

The involved tables looks like this:
CREATE TABLE tracks (
id int(10) unsigned NOT NULL auto_increment,
url text NOT NULL,
audio tinyint(1) unsigned default NULL,
PRIMARY KEY (id),
KEY trackUrlIndex (url(255)),
KEY trackUrlAudioIndex (url(255),audio),
KEY trackAudioIndex (audio)
) ENGINE=InnoDB;

CREATE TABLE track_statistics (
url text NOT NULL,
musicbrainz_id varchar(40) default NULL,
rating int(10) unsigned default NULL,
KEY url_musicbrainz (url(255),musicbrainz_id)
) ENGINE=InnoDB;

And some SQL statements to insert data in them if you want to test it:
INSERT INTO `track_statistics` VALUES ('file:///D:/music/artist1/album1/track1.mp3',NULL,40),('file:///D:/music/artist1/album1/track2.mp3',NULL,60);
INSERT INTO `tracks` VALUES (1,'file:///D:/music/artist1/album1/track1.mp3',1),(2,'file:///D:/music/artist1/album1/track2.mp3',1);

When running the SELECT I'm trying to get to work with explain I get:
explain select tracks.url from tracks left join track_statistics on tracks.url = track_statistics.url where audio=1 and track_statistics.url is null;

MySQL 4.0.24
table type possible_keys key key_len ref rows Extra
================ ==== =============== =============== ======= ========== ==== =======================
tracks ref trackAudioIndex trackAudioIndex 2 const 1 Using where
track_statistics ref url_musicbrainz url_musicbrainz 257 tracks.url 1 Using where; Not exists

MySQL 4.1.16
id select_type table type possible_keys key key_len ref rows Extra
== =========== ===== ==== =============== === ======= ====== ==== =====
1 SIMPLE tracks ref trackAudioIndex trackAudioIndex 2 const 1 Using where
1 SIMPLE track_statistics ALL url_musicbrainz (NULL) (NULL) (NULL) 2 Range checked for each record (index map: 0x1); Not exists

The track_statistics table can't contain a reference to the tracks.id in my setup so thats not an option, the reference between the tables needs to be the url column.

The problem is the url_musicbrainz index. I have also tried "force index url_musicbrainz" but the index is still not used.

Does anyone have an idea how to get this index to work in mysql 4.1 and later releases ?

I have previously posted this messages in the newbie forum, so there are some more details in this thread in the newbie forum also:
http://forums.mysql.com/read.php?10,87609,87609#msg-87609

---------------------
Erland Isaksson

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with index and left join
4276
May 05, 2006 03:04PM
2175
May 09, 2006 11:48PM


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.