优化 MySQL 选择(性能不佳)

Question

我有 3 个表：

T1 约 500.000 行T2 1.600行t3 50.000行

我的SQL查询需要（大约）15秒才能在phpmyadmin中显示结果。

    SELECT * 
    FROM (
      SELECT NULL AS post_subject, t1.id_rec, t1.name, t1.id_cat, t1.date_of_record
      FROM records AS t1
       INNER JOIN categories AS t3 ON t1.id_cat = t3.id_cat AND t3.private =0
     UNION 
      SELECT post_subject, NULL , NULL , NULL , post_time
      FROM posts
     ORDER BY date_of_record DESC 
     LIMIT 10
    ) a

索引是：

records (t1):
 id_rec = PRIMARY
 name = INDEX
 id_cat = INDEX
categories (t3):
 id_cat = PRIMARY
posts:
 post_subject = INDEX

目标是获得根据DATE_OF_RECORD&&POST_TIME排序的10行。记录表和帖子没有连接，它们表示不同的数据。我正在尝试从我的数据中获取某种"日志"......（t1&&t3），（帖子）....随着时间的流逝...

你能帮我优化吗？

Answer 1

您也可以

尝试将LIMIT 10添加到UNION的第一部分。如果你真的只想要 10 个，你会LIMIT 10最终结果：要获得两组中最新的 10 个，您应该只将ORDER BY和LIMIT放在外面，但如果您可以忍受这一点，它可能会更快：

SELECT *
FROM ((
  SELECT NULL AS post_subject, t1.id_rec, t1.NAME, t1.id_cat, t1.date_of_record
  FROM records AS t1
  INNER JOIN categories AS t3
    ON t1.id_cat = t3.id_cat
      AND t3.private = 0
  ORDER BY date_of_record DESC LIMIT 10)      
  UNION      
  (SELECT post_subject, NULL, NULL, NULL, post_time
  FROM posts
  ORDER BY post_time DESC LIMIT 10)
  ) a
ORDER BY date_of_record DESC LIMIT 10

此外，请确保在posts.post_time和records.date_of_record上具有索引，以使其执行速度更快。