I have just recently gained a much greater knowledge on how indexes work and am now going through my sites trying to fix them to run faster with an optimized database. The below query is returning in the extra field 'Using where; Using temporary; Using filesort' and I figure this is one of the main reasons it is performing poorly. I am wondering if anyone has any ideas as to how I can optimize this query so it doesn't return that when I run EXPLAIN on it.
I have added an index that includes 'style' and 'name'.
SELECT id, artist, name, style, avg_vote, votes, updated FROM tabs WHERE SUBSTRING(SOUNDEX(name), 1, 7) = SUBSTRING(SOUNDEX('linkin park'), 1, 7) && style = 'Guitar' || LOCATE('linkin park', name) > '0' && style = 'Guitar' GROUP BY name, artist