代码之家  ›  专栏  ›  技术社区  ›  Gajendra Bang

使用多个查询还是联合多个查询,哪一个会更快?

  •  2
  • Gajendra Bang  · 技术社区  · 15 年前

    多个或 用联合联接的多个查询的查询 会更快吗?我只联接了两个表以使其简单化,但联接中可以有更多的表。

        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%'  
    
    3 回复  |  直到 15 年前
        1
  •  0
  •   Prescott    15 年前

    我不知道你的工会怎么会更快。您必须扫描并加入3次,其中与第一次一样,您只扫描一次,对于每个加入的记录,您可以应用条件。

        2
  •  0
  •   Karelzarath    15 年前

    IN 合同条款 WHERE

    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%')
    

    在筛选列(在本例中是publishername)上使用适当的索引,MySQL将优化每个片段,并更有效地组合它们。