SQL:链接提高效率 [英] SQL: Chaining Joins Efficiency

问题描述

我的WordPress插件中有一个查询，如下所示:

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

我对使用JOIN是陌生的，我想知道在一个查询中使用这么多的JOIN有多有效.将它拆分成多个查询是否更好?

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?

可以在此处找到数据库架构.

推荐答案

JOIN如果索引了索引，通常还不错. LEFT JOIN几乎总是对性能造成影响，如果可能，应避免使用它.区别在于，即使要连接的列为NULL，LEFT JOIN也会连接已连接表中的所有行.普通的(直的)JOIN只是将匹配的行连接起来.

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.

这篇关于SQL:链接提高效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！