mysql 多表关联 性能,MySQL多表联接查询性能问题

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.

解决方案

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.