倒序索引 sql优化

[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);
但是查询结果不是严格意义上按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
我如何修改这个语句使它在执行时使用上面那个索引呢？

[BlueskyWide]

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]

[willowleaf]

感觉应该是一样的

[willowleaf]

结果是一样的

[Andy__Huang]

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而不加括号是不起作用的

[Andy__Huang]

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 分别写在三行
这样结构看上去很清析

[willowleaf]

四楼的伙计，我原来的语句功能相当于：
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
这两个括号加不加都不影响效率的

[willowleaf]

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

[BlueskyWide]

看一下：

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