I have a table that is defined like this:
CREATE TABLE customscan_track_attributes ( id bigint(20) unsigned NOT NULL auto_increment, track int(10) default NULL, url varchar(511) collate utf8_unicode_ci NOT NULL, musicbrainz_id varchar(40) collate utf8_unicode_ci default NULL, module varchar(40) collate utf8_unicode_ci NOT NULL, attr varchar(255) collate utf8_unicode_ci NOT NULL, value varchar(255) collate utf8_unicode_ci default NULL, valuesort varchar(255) collate utf8_unicode_ci default NULL, extravalue varchar(255) collate utf8_unicode_ci default NULL, valuetype varchar(255) collate utf8_unicode_ci default NULL, PRIMARY KEY (id), UNIQUE KEY id (id), KEY musicbrainzIndex (musicbrainz_id), KEY urlIndex (url(255))) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
And I want to perform a select which looks like this:
select customscan_track_attributes.attr,customscan_track_attributes.attr,substr(customscan_track_attributes.attr,1,1) from customscan_track_attributes join customscan_track_attributes attr1 on attr1.module=‘mixedtag’ and attr1.attr=‘GENRE’ and attr1.extravalue=‘1’ and customscan_track_attributes.track=attr1.track join customscan_track_attributes currentattr on currentattr.module=‘mixedtag’ and currentattr.attr=‘YEAR’ and currentattr.extravalue=‘1991’ and customscan_track_attributes.track=currentattr.track where customscan_track_attributes.module=‘mixedtag’ and ((customscan_track_attributes.attr=‘YEAR’ and customscan_track_attributes.extravalue not in (‘1991’)) or (customscan_track_attributes.attr=‘GENRE’ and customscan_track_attributes.extravalue not in (‘1’)) or customscan_track_attributes.attr not in (‘GENRE’,‘YEAR’)) group by customscan_track_attributes.attr order by customscan_track_attributes.attr
When I run explain I get this:
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE customscan_track_attributes ALL \N \N \N \N 163701 Using where; Using temporary; Using filesort1 SIMPLE attr1 ALL \N \N \N \N 163701 Using where1 SIMPLE currentattr ALL \N \N \N \N 163701 Using where
I’ve tried setting up some different index but always end up with one row with “using temporary” and “using filesort”.
Can someone please help me setting up the correct index ?
I’m not sure if this matters, but the first join with “attr1” can be repeated, so there can be a similar one for an “attr2” and “attr3” but with different values on attr and extravalue columns.