需要强制索引为select才能更快地工作吗?

vnzz0bqm  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(397)

我有一个大的选择,当我使用解释它显示:

我需要使用 force index 使用 (user) 作为pp表上的索引(键)。
强制索引后,它将删除中的用户(键) pp2 table。所以我需要使用力指数 (user)pp2 我也是。
强制打开索引后 pp 以及 pp2 table p 将使用 primary 作为索引自动和所有将工作良好。
我的问题是,问题是什么?为什么mysql不使用可能的\u键 user 作为pp表中的键?
为什么在我强迫之后 pp 使用 user 钥匙会把钥匙弄坏的 pp2 ? 在其上添加key=null。为什么强迫之后 pp 以及 pp2 使用user作为键,表p将使用primary作为键,所有这些都可以正常工作吗?
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, 

linked.id as shared_id, linked.titulo as shared_titulo, linked.user as shared_user_id, c2.user as shared_nick, linked.foto as shared_foto, pp2.foto as shared_perfil,
count(DISTINCT share_count.id) as shares_count

from posts p 

join cadastro c on p.user=c.id 
left join profile_picture pp force index (user) 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 = 1

left join posts linked on linked.id = p.post_share
left join cadastro c2 on linked.user=c2.id
left join profile_picture pp2 force index (user) on linked.user = pp2.user
left join posts share_count on share_count.post_share = p.id and share_count.delete=0

where (p.user in (select following from following where user =1 and block=0) or p.user=1) and p.delete=0
group by p.id
order by p.id desc limit 15

资料图片:

CREATE TABLE `profile_picture` (
  `user` int(11) UNSIGNED NOT NULL,
  `foto` varchar(400) NOT NULL,
  UNIQUE KEY `user` (`user`)
) ENGINE=InnoDB ;

谢谢。

8nuwlpux

8nuwlpux1#

为了提高性能,您还可以避免in子句,并为此使用内部联接,例如:(i hope)

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, 

  linked.id as shared_id, linked.titulo as shared_titulo, linked.user as shared_user_id, c2.user as shared_nick, linked.foto as shared_foto, pp2.foto as shared_perfil,
  count(DISTINCT share_count.id) as shares_count

  from posts p 

  join cadastro c on p.user=c.id 
  left join profile_picture pp force index (user) 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 = 1

  left join posts linked on linked.id = p.post_share
  left join cadastro c2 on linked.user=c2.id
  left join profile_picture pp2 force index (user) on linked.user = pp2.user
  left join posts share_count on share_count.post_share = p.id and share_count.delete=0

  inner join following f on (f.following = p.user and f.user=1 and f.block = 0 and p.delete=0)  
      OR  ( p.user=1 and p.delete =0)

  group by p.id
  order by p.id desc limit 15

查看pp表,在该表中用户中有一个唯一索引,您可以尝试在create中将此条件更改为pk条件

CREATE TABLE `profile_picture` (
    `user` int(11) UNSIGNED NOT NULL,
    `foto` varchar(400) NOT NULL,
   PRIMARY KEY   (`user`)
  ) ENGINE=InnoDB ;

相关问题