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