# multiple joins on same table: Can i Optimizing index ?

**URL:** <https://forums.percona.com/t/multiple-joins-on-same-table-can-i-optimizing-index/5547>\
**Category:** Other MySQL® Questions\
**Created:** [April 16, 2017, 4:16am UTC](https://forums.percona.com/t/multiple-joins-on-same-table-can-i-optimizing-index/5547 "2017-04-16T04:16:20Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![takeshi14121990](https://avatars.discourse-cdn.com/v4/letter/t/3da27b/32.png) [@takeshi14121990](https://forums.percona.com/u/takeshi14121990)\
**Post date:** [April 16, 2017, 4:16am UTC](https://forums.percona.com/t/multiple-joins-on-same-table-can-i-optimizing-index/5547/1 "2017-04-16T04:16:20Z")

</div>

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.