多个查询与联合多个查询，哪个会更快？

发布于 2024-10-02 01:09:17 字数 868 浏览 2 评论 0原文

顾名思义，使用多个或的查询会更快，还是使用联合连接的多个查询的查询会更快？为了简单起见，我只连接了两个表，但连接中可以有更多表。

例如，

    select isbn, booktitle from book as b left join publisher as p
        on b.publisherid = p.publisherid
        where 
           booktitle like '%mysql%' or
           isbn like '%mysql%' or
           publishername like '%mysql%'

与

      select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    booktitle like '%mysql%'  
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    isbn like '%mysql%' 
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
    where 
    publishername like '%mysql%'

原文

As the name suggests, a query with multiple or will be faster or a query with multiple query joined with union will be faster? I have joined only two tables to make it simple but there can be more tables in join.

for example,

    select isbn, booktitle from book as b left join publisher as p
        on b.publisherid = p.publisherid
        where 
           booktitle like '%mysql%' or
           isbn like '%mysql%' or
           publishername like '%mysql%'

      select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    booktitle like '%mysql%'  
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    isbn like '%mysql%' 
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
    where 
    publishername like '%mysql%'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

装迷糊 2024-10-09 01:09:17

我不知道你的工会会更快。您必须扫描并加入 3 次，而在第一次中，您只需扫描一次，并且对于每个加入的记录，您都可以应用条件。

回复收藏 0 原文

執念 2024-10-09 01:09:17

最上面的一个会更有效，因为您只查询数据一次。也就是说，如果您查询不会返回的数据，最好将这些条件放在 WHERE 的 IN 子句中。在您提供的示例中，它看起来像这样：

SELECT isbn, booktitle
FROM book as b 
WHERE 
   booktitle LIKE '%mysql%' or
   isbn like '%mysql%' or
   b.publisherid in (SELECT publisherid FROM publisher WHERE publishername like '%mysql%')

通过过滤列（在本例中为发布者名称）上的适当索引，MySQL 将优化每个部分并更有效地组合它们。

The top one will be more efficient since you're only querying the data once. That said, if you're querying against data you will not be returning, it's even better to put those criteria in an IN clause in the WHERE. In your provided example, it would look like this:

SELECT isbn, booktitle
FROM book as b 
WHERE 
   booktitle LIKE '%mysql%' or
   isbn like '%mysql%' or
   b.publisherid in (SELECT publisherid FROM publisher WHERE publishername like '%mysql%')

With proper indexes on the filtering columns (publishername, in this case), MySQL will optimize each piece and combine them much more efficiently.

回复收藏 0 原文