倒序索引 sql优化

2008-11-12 04:26:36 294 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MySQL深分页 + 多字段排序场景的优化方案【三百万级数据量】 MySQL深分页 + 多字段排序场景优化方案阅读详情

执行下面这个sql语句
select id, title, create_on, has_pic from tbl_info_article where is_recommend = 0 and category_id between 5001 and 5008 and has_check=0 or is_recommend= 0 and category_id between 5001 and 5008 and has_check=1 and can_pass=1 order by id desc
建立索引的语句是create index ind_article_main_1 on tbl_info_article(is_recommend,category_id,has_check,id desc);
但是查询结果不是严格意义上按id倒序排列的，请问这个问题怎么解决呢？
另一个问题是
还有一个查询语句是
select id, title, create_on, has_pic from tbl_info_article where category_id =1003 and has_check=0 or category_id=1003 and has_check=1 and can_pass=1
我如何修改这个语句使它在执行时使用上面那个索引呢？

...全文

使用sysbench对Oracle做基准测试(-) 安装篇 sysbench是一款开源的多线程性能测试工具，可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。阅读详情

willowleaf

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

BlueskyWide 2008-11-13

打赏
举报

看一下：

http://www.hackhome.com/InfoView/Article_165508.html

willowleaf 2008-11-13

打赏
举报

5楼的伙计，给点儿实质性的意见好不好？

willowleaf 2008-11-13

打赏
举报

四楼的伙计，我原来的语句功能相当于：
select id, title, create_on, has_pic
from tbl_info_article
where
(is_recommend = 0
and category_id between 5001 and 5008
and has_check=0 )
or
（is_recommend= 0
and category_id between 5001 and 5008
and has_check=1
and can_pass=1
）
order by id desc
这两个括号加不加都不影响效率的

Andy__Huang 2008-11-12

打赏
举报

select id, title, create_on, has_pic from tbl_info_article where category_id =1003 and has_check=0 or category_id=1003 and has_check=1 and can_pass=1
应该改为:
select id, title, create_on, has_pic
from tbl_info_article
where category_id =1003 and (has_check=0 or has_check=1) and can_pass=1

你的语法结构看上去很乱,应该是select,from ,where 分别写在三行
这样结构看上去很清析

Andy__Huang 2008-11-12

打赏
举报

select id, title, create_on, has_pic from tbl_info_article where is_recommend = 0 and category_id between 5001 and 5008 and has_check=0 or is_recommend= 0 and category_id between 5001 and 5008 and has_check=1 and can_pass=1 order by id desc
应该改为:
select id, title, create_on, has_pic
from tbl_info_article
where is_recommend = 0 and category_id between 5001 and 5008
and (has_check=0 or is_recommend= 0) and category_id between 5001 and 5008 and has_check=1 and can_pass=1
order by id desc
因为你了有or而不加括号是不起作用的

willowleaf 2008-11-12

打赏
举报

结果是一样的

willowleaf 2008-11-12

打赏
举报

感觉应该是一样的

BlueskyWide 2008-11-12

打赏
举报



select id, title, create_on, has_pic

  from tbl_info_article

 where is_recommend = 0

   and category_id between 5001 and 5008

   and has_check = 0

    or is_recommend = 0

   and category_id between 5001 and 5008

   and has_check = 1

   and can_pass = 1

 order by is_recommend, category_id, has_check, id desc;  --使用和索引一样的排序，是吗？

[Quote=引用楼主 willowleaf 的帖子:]
执行下面这个sql语句
select id, title, create_on, has_pic from tbl_info_article where is_recommend = 0 and category_id between 5001 and 5008 and has_check=0 or is_recommend= 0 and category_id between 5001 and 5008 and has_check=1 and can_pass=1 order by id desc
建立索引的语句是create index ind_article_main_1 on tbl_info_article(is_recommend,category_id,has_check,id desc);
但是查询结果不是…
[/Quote]

倒序索引 sql优化

相关推荐