GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimizing index for multiple joins on same table ?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimizing index for multiple joins on same table ?

    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.trac k 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.
Working...
X