sql优化
select * from px_user_wallpaper_info where state in ( 3 , 1 , 2 , 4 , 6 ) and wp_id in (select wp_id from px_user_wallpaper_class_relation_info whereclass_id not in("12","1"))这条语句该如何优化呢 如果有权限建临时表强烈建议走临时表 select * from px_user_wallpaper_info a
LEFT JOIN (select wp_id from px_user_wallpaper_class_relation_info whereclass_id not in("12","1")) b
ON a.wp_id = b.wp_id
where state in ( 3 , 1 , 2 , 4 , 6 );
wp_id 建索引 projectD 发表于 2022-2-23 21:55
select * from px_user_wallpaper_info a
LEFT JOIN (select wp_id from px_user_wallpaper_class_relatio ...
我咋感觉, 你写的和他的效率差不多??? select * from px_user_wallpaper_info p where p.state in ( 3 , 1 , 2 , 4 , 6 ) and
exists(select 1 from px_user_wallpaper_class_relation_info a wherea.class_id not in("12","1") and a.wp_id = p.wp_id ) 看你这两个表的数据量、索引。statein ( 3 , 1 , 2 , 4 , 6 )要改成用(state = 3 or state=2 or state =1)，不要用in,in不命中索引，会慢的
后边的class_id not in("12","1")也要改成= 使用explain 分析一下sql，一般情况下达到ref 或range都可以接受，条件尽量都覆盖索引 我就是进来学习的，你们继续，不用管我 projectD 发表于 2022-2-23 21:55
select * from px_user_wallpaper_info a
LEFT JOIN (select wp_id from px_user_wallpaper_class_relatio ...
是不是应该 select a.* 过来看看看而已，不用理我
页:
[1]
2