mysql - 选择在多行表上花费大量时间

Question

我有这个选择:

select 'like' prefix
     , l.post
     , l.data as data
     , l.user
     , concat(k.user, ' liked you') as logs 
  from likes l 

inner join posts p on l.post = p.id 
inner join cadastro k on l.user = k.id 
where p.user = 1 and l.user <> p.user

order by data desc
limit 10

耗时 2.3993 秒。

有什么改进的想法吗？

`likes` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` int(11) UNSIGNED NOT NULL,
  `post` int(11) UNSIGNED NOT NULL,
  `data` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_post` (`user`,`post`),
  KEY `post_user` (post, user),
  FOREIGN KEY (`user`) REFERENCES cadastro (`id`),
  FOREIGN KEY (`post`) REFERENCES posts (`id`) ON DELETE CASCADE
)

`posts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

`cadastro` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

有什么办法可以加快速度吗？我还尝试在喜欢的数据上添加索引，但没有发现任何差异。

Answer 1

这是您的查询:

select 'like' as prefix, l.post, l.data as data, l.user,
        concat(k.user, ' liked you') as logs
from likes l join
     posts p
     on l.post = p.id oin
     cadastro k
     on l.user = k.id 
where p.user = 1 and l.user <> p.user
order by data desc
limit 10;

对于此查询，您需要在 post(user, id)、likes(post, user, data) 和 cadastro(id, user )。

您无法消除 order by 的开销，但这应该会加快查询的连接和过滤部分。