Remove 'Using temporary' & 'Using filesort'

[GoldFire]

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 

[r937]

first of all, replace the non-standard && and || connectors with ANDs and ORs, and use parentheses to simplify the WHERE clause

second, replace GROUP BY with ORDER BY, since you aren't doing any aggregating

these two changes might not improve the performance, but they'll make your query easier to understand and maintain

select id
     , artist
     , name
     , style
     , avg_vote
     , votes
     , updated 
  from tabs 
 where style = 'Guitar' 
   and (
       locate('linkin park', name) > '0' 
    or substring(soundex(name),1,7) 
     = substring(soundex('linkin park'),1,7) 
       )
order 
    by name
     , artist

finally, consider not sorting the results

[GoldFire]

Hey thanks for the reply. Does using 'AND' and 'OR' instead make any difference to speed? I am just wondering because I have always used '||' and '&&' and if the other way is faster I might want to consider going through and changing all of those. Also, I need to group them because there will be several results with the exact same name sometimes and when there are and the person clicks on the result it takes them to a page with all of those. Another thing, I need to sort the results because this query is part of the search feature on my site which is mainly what the site is focused arround.

[r937]

there cannot be several results with the exact same name if you're only querying a single table and you include the id (presumably the primary key)

and i really don't think you need to sort the results -- think about how many artists actually satisfy your "linkin park" criterion, and ask yourself, is it really nececssary to sort them

use ANDs and ORs because they are standard sql

i wouldn't go back and retrofit all your existing queries, but if you should ever write sql for any other database (or wish to port your application to a different backend database) you will have to get rid of those proprietary mysql connectors

[GoldFire]

Oh, sorry i guess I didn't specify. Where it says 'linkin park', that is just an example search term. Whatever the person searched for will go there. Also, I am not saying there will be duplicate rwos, but rather there are usually more than one result with the same song name and artist, just different variations of the tab or lyric.