使用左联接大小写按以下顺序查询10倍慢

Question

我有一个相对较长的查询(张贴在下面供参考)。

我尝试过调试查询速度如此慢的原因(2秒)，并最终找到了原因。

在查询结束时，我这样做：

  ORDER BY 
    -- order by date
    DATE(p.date) DESC, 
    -- order by followed people
    CASE WHEN n.id IS NULL THEN '0' ELSE '1' END DESC -- this case takes the query from 20ms to 2 seconds

如果我删除案例顺序，它将在大约20毫秒内执行。

为什么会这样呢？

当我使用EXPLAIN运行查询时，我注意到情况会在额外的字段中添加“使用临时的”。

见下文的解释查询：

使用CASE WHEN n.id IS NULL THEN '0' ELSE '1' END DESC按以下顺序解释查询

​

​

按以下顺序解释不带 CASE WHEN n.id IS NULL THEN '0' ELSE '1' END DESC的查询CASE WHEN n.id IS NULL THEN '0' ELSE '1' END DESC

​

​

完整的查询(如果有帮助的话)：

  SELECT 
    -- feed type
    '1' AS feed_type, 
    -- fetch post data
    p.id, 
    p.receiver, 
    p.date, 
    p.message, 
    p.system_msg, 
    p.type AS post_type, 
    -- fetch author data
    u.user_id, 
    u.firstname, 
    u.lastname, 
    u.type, 
    u.permalink, 
    av.file AS avatar_file, 
    -- fetch receiever data
    u2.user_id AS receiver_user_id, 
    u2.firstname AS receiver_firstname, 
    u2.lastname AS receiver_lastname, 
    u2.permalink AS receiver_permalink, 
    u2.type AS receiver_type, 
    -- fetch post comment count
    (
      SELECT 
        COUNT(*) 
      FROM 
        edu_posts pc 
      WHERE 
        pc.comment = p.id 
        AND pc.deleted IS NULL
    ) as commentCount, 
    -- fetch post like count
    (
      SELECT 
        COUNT(*) 
      FROM 
        edu_likes l 
      WHERE 
        l.like_entity = p.id
    ) as likeCount, 
    -- user follow state
    CASE WHEN n.id IS NOT NULL THEN '1' ELSE '0' END as is_following, 
    -- check if user likes post
    CASE WHEN l.like_id IS NOT NULL THEN '1' ELSE '0' END as user_likes 
  FROM 
    edu_posts p
    INNER JOIN -- author information
    edu_users u ON u.user_id = p.author 
    LEFT JOIN -- author avatar
    edu_avatars av ON av.fk = p.author 
    AND av.temp = 0 
    AND av.fk_type = 1 
    LEFT JOIN -- receiver information (if any)
    edu_users u2 ON u2.user_id = p.receiver 
    LEFT JOIN -- check if author/receiver is followed by current user
    edu_notification_list n ON n.user = 1 
    AND n.following = 1 
    AND (
      n.fk = p.author 
      OR n.fk = p.receiver
    ) 
    AND (
      (
        n.type = 5 
        AND p.type = 3
      ) 
      OR (
        n.type = 2 
        AND p.type = 1
      )
    ) 
    LEFT JOIN -- check if user likes the post
    edu_likes l ON l.like_entity = p.id 
    AND l.like_author = 1 
  WHERE
    p.deleted IS NULL 
    AND p.comment IS NULL 
    AND (
      p.id = p.comment 
      OR 1 = 1
    ) 
    AND (
      n.id IS NOT NULL 
      OR p.system_msg = 0
    ) 
  ORDER BY 
    -- order by date
    DATE(p.date) DESC, 
    -- order by followed people
    CASE WHEN n.id IS NULL THEN '0' ELSE '1' END DESC 
  LIMIT 
    20 OFFSET 0

注:如果您想看其他桌子，请告诉我。

Answer 1

您执行无法使用索引的操作。相反，试着

ORDER BY DATE(p.date) DESC, 
         n.id IS NULL ASC