MySQL multiple table join query performance issue

Question

I am trying to write a query for a search.

I have a meta table with columns:

ID | meta_key | meta_value

I want to pull a search like the following:

SELECT ID FROM meta_table WHERE meta_key = 'title' AND meta_value = 'searchword'

However, the complication is that I want to pull this from multiple sets of (multiple meta_keys and multiple meta_values) if that makes sense.
Here is one of the generated queries:

SELECT name_table_dan.ID FROM 
    (SELECT user_id as ID FROM wp_usermeta WHERE (meta_key = 'first_name' or meta_key = 'last_name') AND meta_value LIKE '%dan%') as name_table_dan 
INNER JOIN (SELECT user_id as ID FROM wp_usermeta WHERE (meta_key = 'first_name' or meta_key = 'last_name') AND meta_value LIKE '%newman%') as name_table_newman
    ON name_table_newman.ID=name_table_dan.ID 
    INNER JOIN (SELECT user_id as ID FROM wp_usermeta WHERE meta_key = 'user_industry' AND meta_value LIKE '%games%') as industry_table_games 
    ON industry_table_games.ID=name_table_newman.ID
    INNER JOIN (SELECT user_id as ID FROM wp_usermeta WHERE (meta_key = 'user_firm' or meta_key = 'company') AND meta_value LIKE '%incorp%') as company_table_incorp
    ON company_table_incorp.ID=industry_table_games.ID
    INNER JOIN (SELECT user_id as ID FROM wp_usermeta WHERE meta_key = 'user_location' AND meta_value LIKE '%denver%') as location_table_denver
    ON location_table_denver.ID=company_table_incorp.ID GROUP BY ID;

Sorry for the long query, but just trying to make my point.

The query works just fine the problem being that when some of these searches - namely, the select generated tables towards the beginning of the query - return large result sets, the query time slows down dramatically, and if I get a few large result sets then the query pretty much hangs the system to where I have to end the process manually.

I think the problem may lie with MySQL (I believe postgre does not have this problem). I looked around and discovered that mysql has problems with nested queries and joins.

I am asking what the best way to handle this would be? I ideally would like to avoid have multiple database calls as I feel this should be unnecessary, so this is not an answer I am looking for unless there is no alternative.

Answer 1

 SELECT name_table_dan.ID FROM wp_usermeta name_table_dan
     INNER JOIN wp_usermeta name_table_newman ON name_table_dan.ID = name_table_newman.ID
      ...
 WHERE 
   (name_table_dan.meta_key = 'first_name' OR name_table_dan.meta_key = 'last_name' AND name_table_dan.meta_value LIKE '%dan%')
   AND
   (name_table_newman.meta_key = 'first_name' OR name_table_newman.meta_key = 'last_name' AND name_table_dan.meta_value LIKE '%newman%')
 ...

It is not the complete query translation of yours, but you get the idea. If MySQL is having problems with so many subqueries, try this one.