CODEWITHSUNDEEP
sqlmysqlperformance
Brandon - Free Palestine
Brandon - Free Palestine

Reputation: 16656

SQL: Chaining Joins Efficiency

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.

Upvotes: 3

Views: 13335

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107746

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")

Upvotes: 2

Cfreak
Cfreak

Reputation: 19309

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.

Upvotes: 8

Matej Baćo
Matej Baćo

Reputation: 1332

Try explaining the query to see what is going on and if your select if optimized. If you haven't used explain before read some tutorials:

http://www.learn-mysql-tutorial.com/OptimizeQueries.cfm

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Upvotes: 1

Related Questions