查询执行时间较慢

Question

SELECT p.id, 
       p.title, 
       p.slug, 
       p.content, 
       (SELECT url 
        FROM   gallery 
        WHERE  postid = p.id 
        LIMIT  1) AS url, 
       t.name 
FROM   posts AS p 
       INNER JOIN termrel AS tr 
         ON ( tr.object = p.id ) 
       INNER JOIN termtax AS tx 
         ON ( tx.id = tr.termtax_id ) 
       INNER JOIN terms AS t 
         ON ( t.id = tx.term_id ) 
WHERE  tx.taxonomy_id = 3 
       AND p.post_status IS NULL 
ORDER  BY t.name ASC 

这个查询大约花了0.2407s来执行。如何让它变得更快？

Answer 1

相关子查询的性能可能不佳，因为它们是executed row by row.。要解决这个问题，请将您的相关子查询移动到一个常规子查询/派生表中，并连接到它。然后，它将不会逐行执行整个返回的结果集，因为它将在select语句之前执行。

确认相关子查询的mysql特定链接在mysql中不是最佳选择。How to optimize Answer indicating msql notoriously bad at optimizing correlated subqueries

我使用sql-，但我确信mysql服务器的原理也是一样的，所以我希望这至少给你指明了正确的方向。您将需要分区/返回每个贷款的一个结果，也许一些人可以加入mysql特定的语法，我可以更新我的答案

select
    p.id
   ,p.title
   ,p.slug
   ,p.content
   ,t.name
   ,mySubQuery.value
from
    posts as p
    inner join termrel as tr
    on ( tr.object = p.id )
    inner join termtax as tx
    on ( tx.id = tr.termtax_id )
    inner join terms as t
    on ( t.id = tx.term_id )

    left join (
                -- use MYSQL function to partition the reslts and only return 1, I use sql-server, not sure of the RDMS specific syntax
                select
                id
                ,url
                from
                    gallery
                limit 1
              ) as mySubquery
    on mySubquery.id = p.id


where
    tx.taxonomy_id = 3
    and p.post_status is null
order by
    t.name asc