执行下面这个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
我如何修改这个语句使它在执行时使用上面那个索引呢?
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
我如何修改这个语句使它在执行时使用上面那个索引呢?
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; --使用和索引一样的排序,是吗?
应该改为:
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而不加括号是不起作用的
应该改为:
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 分别写在三行
这样结构看上去很清析
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
这两个括号加不加都不影响效率的