where 조건에 함수가 있으면, index를 쓰지 않는다?

불당   
   조회 5602  

지식게시판 스킨에 보니 이런게 있네요. 이러면 idnex를 못 탑니다.

 

select count(*) as cnt from g4_write_qna_agency where mb_id = 'echo4me' and wr_2 = '' and wr_is_comment = 0 and wr_datetime > adddate(curdate(), interval -83C5D3 day)

--

 

MySQL won't/can't use indexes where you apply a function to a column in a where condition.

In your case it's the DATE() function that's stopping the query use an index.

Off the top of my head instead of:
mysql> explain SELECT news_id,news_title,news_hit FROM news WHERE DATE(news_date) = '2007-03-14' ORDER BY news_hit DESC LIMIT 10;

You could do something a bit hacky such as :
mysql> explain SELECT news_id,news_title,news_hit FROM news WHERE news_date between '2007-03-14 00:00:00' and '2007-03-15 00:00:00' ORDER BY news_hit DESC LIMIT 10;

which would use the index on news_date.

I'm sure there's a more elegant way to do this but it's a start!

BTW, depending on your data distribution etc, a multi-col index on (news_date, news_hit) should stop this query needing to filesort to do the order by too.

Toasty

- opencode.co.kr -


제목Page 7/9
     
2015-09   85821   불당
2011-12   204961   불당
2016-06   40389   불당
2016-05   68169   불당
2015-09   83932   불당
2010-10   5772   불당
2010-10   4886   불당
2010-10   5715   불당
2010-10   8830   불당
2010-10   16052   불당
2010-10   4529   불당
2010-06   23464   불당
2010-06   9895   불당
2010-06   3812   불당
2010-04   5603   불당
2010-03   7041   불당
2010-03   9208   불당
2010-03   4572   불당
2010-03   4282   불당
2010-03   5448   불당
2010-03   3397   불당
2010-03   5706   불당
2010-03   3568   불당
2010-03   3766   불당
2009-11   5079   불당