PDA

View Full Version : MySQL problem with index



erland
2006-05-04, 22:02
I have already posted this on the forums at mysql.com, but I thought I post it here also in case we have any mysql expert looking at the slimserver forum/maillists.

The problem is that the index I have defined in the TrackStat table in mysql works in mysql 4.0.24 but is not used in mysql 4.1 or later.

The table for TrackStat is defined as follows:


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


The SQL I am running looks as follow (a bit simplified):
select tracks.url from tracks left join track_statistics on tracks.url = track_statistics.url where audio=1 and track_statistics.url is null;

The problem is that the url_musicbrainz index is not used in mysql 4.1 or later, while it works perfectly in mysql 4.0.24.

The output when running the select with explain:


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


If you want to help and want to setup a configuration to test it you can either install the TrackStat plugin and test it directly in your slimserver database (if you are using mysql), or if you have access to an empty mysql database you can use the attached .sql file to create two simplified tables which have the same problem.

Any help or ideas are appreciated.