mysql - 优化mysql select(性能差)

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

目标是获得 10 行，这些行是根据 DATE_OF_RECORD && POST_TIME 排序的。记录表和帖子没有连接，它们代表不同的数据。我正在尝试从我的数据中获取某种“日志”... (t1&&t3),(posts)... 随着时间的推移...

你能帮我优化一下吗？

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 上有索引，以加快执行速度。