MySQL query running really slow for large database

Question

I am trying to extract duplicate rows from my database. I have a listings table and a listings_meta table which holds additional information. The listings_meta table has over 16,000 rows. When I run the query below it runs REALLY slow.

I don't understand why. Is there something wrong with my code?

SELECT l.id FROM listings AS l
   LEFT JOIN listings_meta AS lm ON lm.listing_id = l.id AND lm.meta_key = 'email'
   LEFT JOIN listings_meta AS lm2 ON lm2.listing_id = l.id AND lm2.meta_key = 'submitter_email'
   WHERE lm.meta_value = '[email protected]' OR lm2.meta_value = '[email protected]' OR l.listing_title RLIKE 'Test'
   GROUP BY l.id LIMIT 1

Are there certain things I can do to improve load time of large tables in general?

Answer 1

Do you have indexes on listing_id and meta_key? You should have an index on any field you are going to sort by or match on, because otherwise mysql has to go through the whole table to find each row.

You can also prepend EXPLAIN to the statement to see how mysql is doing the lookups.

Answer 2

Indexes

Do you have any indexes - particularly on LISTINGS_META.listing_id?

I re-wrote your query as:

   SELECT l.id 
     FROM listings AS l
LEFT JOIN listings_meta AS lm ON lm.listing_id = l.id 
                             AND lm.meta_key = 'email'
                             AND lm.meta_value = '[email protected]'
LEFT JOIN listings_meta AS lm2 ON lm2.listing_id = l.id 
                              AND lm2.meta_key = 'submitter_email'
                              AND lm2.meta_value = '[email protected]'
    WHERE l.listing_title RLIKE 'Test'
 GROUP BY l.id LIMIT 1

WHERE clause

RLIKE (any of the MySQL regex functionality for that matter) is on par with:

WHERE l.listing_title LIKE '%Test%'

Neither can make use of an index on the LISTING.listing_title column...

The best way to improve the listing_title search would be if the LISTING table is MyISAM, so you could add a Full Text Index to the LISTING.listing_title column in order to use:

WHERE MATCH(l.listing_title) AGAINST ('Test')

...but mind that the minimum word length is 3 characters for Full Text Searching (FTS)....