0

I am trying to optimise this query - currently its taking around 10 seconds to complete.

I believe the slowdown is coming from the REGEXP - the data in the all_artists field is in the format of artist1|artist2|artist3

SELECT * FROM releases WHERE (all_artists REGEXP 
    (SELECT GROUP_CONCAT(releases.artist SEPARATOR '|') 
        FROM releases 
        INNER JOIN charts_extended 
        ON charts_extended.release_id=releases.id 
        WHERE charts_extended.artist='Quickinho' 
        AND releases.all_artists!='Various Artists') 

    OR label_no_country IN 
    (SELECT releases.label_no_country 
    FROM releases 
    INNER JOIN charts_extended 
    ON charts_extended.release_id=releases.id 
    WHERE charts_extended.artist='Quickinho')) 

    AND releases.id NOT IN (SELECT release_id FROM charts_extended WHERE artist='Quickinho') ORDER BY date DESC LIMIT 0,102
Franco
  • 2,678
  • 6
  • 30
  • 51
  • 4
    And that's another example of why [*saving multiple values in a column is a bad idea*](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Nir Alfasi Jul 19 '14 at 08:35
  • I decided to split out the column into a new table - query was getting ridiculous. – Franco Jul 19 '14 at 09:01
  • You'll really need to post the table creation statements and the `EXPLAIN` output or nobody can help you optimise. – Leeft Jul 19 '14 at 09:33

0 Answers0