SQL: Chaining Joins Efficiency

Question

I have a query in my WordPress plugin like this:

SELECT users.*, U.`meta_value` AS first_name,M.`meta_value` AS last_name 
    FROM `nwp_users` AS users 
        LEFT JOIN `nwp_usermeta` U 
            ON users.`ID`=U.`user_id` 
        LEFT JOIN `nwp_usermeta` M 
            ON users.`ID`=M.`user_id` 
        LEFT JOIN `nwp_usermeta` C 
            ON users.`ID`=C.`user_id` 
    WHERE U.meta_key = 'first_name' 
        AND M.meta_key = 'last_name' 
        AND C.meta_key = 'nwp_capabilities' 
    ORDER BY users.`user_login` ASC 
    LIMIT 0,10

I'm new to using JOIN and I'm wondering how efficient it is to use so many JOIN in one query. Is it better to split it up into multiple queries?

The database schema can be found here.

Answer 1

JOIN usually isn't so bad if the keys are indexed. LEFT JOIN is almost always a performance hit and you should avoid it if possible. The difference is that LEFT JOIN will join all rows in the joined table even if the column you're joining is NULL. While a regular (straight) JOIN just joins the rows that match.

Post your table structure and we can give you a better query.

Answer 2

See this comment:

http://forums.mysql.com/read.php?24,205080,205274#msg-205274

For what it's worth, to find out what MySQL is doing and to see if you have indexed properly, always check the EXPLAIN plan. You do this by putting EXPLAIN before your query (literally add the word EXPLAIN before the query), then run it.

In your query, you have a filter AND C.meta_key = 'nwp_capabilities' which means that all the LEFT JOINs above it can be equally written as INNER JOINs. Because if the LEFT JOINS fail (LEFT OUTER is intended to preserve the results from the left side), the result will 100% be filtered out by the WHERE clause.

So a more optimal query would be

SELECT users.*, U.`meta_value` AS first_name,M.`meta_value` AS last_name 
    FROM `nwp_users` AS users 
        JOIN `nwp_usermeta` U 
            ON users.`ID`=U.`user_id` 
        JOIN `nwp_usermeta` M 
            ON users.`ID`=M.`user_id` 
        JOIN `nwp_usermeta` C 
            ON users.`ID`=C.`user_id` 
    WHERE U.meta_key = 'first_name' 
        AND M.meta_key = 'last_name' 
        AND C.meta_key = 'nwp_capabilities' 
    ORDER BY users.`user_login` ASC 
    LIMIT 0,10

(note: "JOIN" (alone) = "INNER JOIN")