SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard GoldFire's Avatar
    Join Date
    Oct 2002
    Location
    Oklahoma City, OK
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Remove 'Using temporary' & 'Using filesort'

    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'.

    PHP Code:
    SELECT idartistnamestyleavg_votevotesupdated FROM tabs WHERE SUBSTRING(SOUNDEX(name), 17) = SUBSTRING(SOUNDEX('linkin park'), 17) && style 'Guitar' || LOCATE('linkin park'name) > '0' && style 'Guitar' GROUP BY nameartist 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    Code:
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard GoldFire's Avatar
    Join Date
    Oct 2002
    Location
    Oklahoma City, OK
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard GoldFire's Avatar
    Join Date
    Oct 2002
    Location
    Oklahoma City, OK
    Posts
    1,517
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •