问题描述
我有一个表following，一个用户可以在其中关注另一个.该表有 200.000 多条记录.我的选择需要一段时间.
I have a table following where one user can follow another.This table has 200.000+ records. and my select is taking a while.
where (p.user in (select following from following where user =1 and block=0 and feed=0) or p.user=1) and p.delete='0'
我有这个 where 来获取 user=1 跟随的 users.这是需要更长时间的派对.
I have this where to get users that user=1 follows. This is the parte that is taking longer.
我的下表:
`following` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`user` INT(11) UNSIGNED NOT NULL REFERENCES cadastro (`id`),
`following` INT(11) UNSIGNED NOT NULL REFERENCES cadastro (`id`),
`block` tinyint(1) NOT NULL DEFAULT 0,
`feed` tinyint(1) NOT NULL DEFAULT 0,
`data` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`user`, `following`)
)
任何想法如何更快地获得关注并改进此表?任何索引或任何想法?
any ideas how to get followings faster and improve this table? any ideas of index or anything?
我的完整sql:
select c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, count(DISTINCT likes.user) as likes_count, count(distinct comentarios.id) as comentarios_count, count(DISTINCT l2.user) as count2
from posts p
join cadastro c on p.user=c.id
left join profile_picture pp on p.user = pp.user
left join likes on likes.post = p.id
left join comentarios on comentarios.foto = p.id and comentarios.delete = 0
left join likes l2 on l2.post = p.id and l2.user = ?
where (p.user in (select following from following where user =? and block=0) or p.user=?) and p.delete='0'
group by p.id
order by p.id desc limit ?
帖子:
KEY `share` (post_share, `delete`),
PRIMARY KEY (`id`)
谢谢.
推荐答案
我不确定您是否只希望提取 following.block=0.在您的 following 表上，您应该有 (user, follow, block) 的索引，这将使其成为仅索引扫描.
I'm not sure if you're looking to pull up only following.block=0. On your following table it looks like you should have an index for (user, following, block) this would make it an index only scan.
摆脱嵌套查询很重要.
这是一个猜测，但我相信您正在寻找以下内容:
Here's a guess but I believe you're looking for the following:
select c.nome,
p.foto,
c.user,
p.user,
p.id,
p.data,
p.titulo,
p.youtube,
pp.foto,
count(DISTINCT likes.user) as likes_count,
count(distinct comentarios.id) as comentarios_count,
count(DISTINCT l2.user) as count2
from posts p
join cadastro c on p.user=c.id
left join profile_picture pp on p.user = pp.user
left join likes on likes.post = p.id
left join comentarios on comentarios.foto = p.id and comentarios.delete = 0
left join likes l2 on l2.post = p.id and l2.user = ?
left join following f on f.user = p.user
where
(p.user = ? and p.delete = '0')
or
(f.user = ? and f.block=0)
group by p.id
order by p.id desc limit ?
这篇关于包含 200.000 多条记录的表需要 4 秒选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！