MySQL query running really slow for large database

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 list开发者_如何学JAVAings_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 = 'test@test.com' OR lm2.meta_value = 'test@test.com' 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?

---

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 = 'test@test.com'
LEFT JOIN listings_meta AS lm2 ON lm2.listing_id = l.id 
                              AND lm2.meta_key = 'submitter_email'
                              AND lm2.meta_value = 'test@test.com'
    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)....

---

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.