How to use where clause in separate datetime(year,month,day)

http://upic.me/i/hq/capture.png

http://upic.me/i/3g/capture.png

I have the table that divide datetime to single field and set these field to index. i would to use where clause in date range ex. between 2010/06/21 to 2011/05/15 I try to use

where concat_ws('-',year,month,day) between '2010/06/21' and '2011/05/15'

it's work because I use concat function to adjust these field like ordinary datetime but it not use index and query slowly.This table has 3 million record

if would to use index I try to this query

where
year 开发者_如何转开发= '2011'
and month between 05 and 06
and day between 21 and 15

It almost work but in last line day between 21 and 15 I can't use this condition I try to solve this problem but I can't find it and change structer table I'm looking for answer

thank you

Now I can OR operation for query thank for your answer In another case if would to find 2009/08/20 to 2011/04/15 It's use longer query and make confusion.Has someone got idea?

If it's a datestamp type, you can just use the where/between clause directly. I would consider switching to that, it's quite faster than a varchar with a custom date format.

WHERE yourdate BETWEEN "2011-05-01" AND "2011-06-15"

Although checking ranges may work for single months, you will find if you're querying between several months to have some margin of error because, if you think about it, you're selecting more than you may necessarily want. Using Datestamp will fix performance and usability issues arising from storing the date in a custom varchar.

Here are the two queries to convert your times around if you're interested:

ALTER TABLE  `yourtable` ADD  `newdate` DATE NOT NULL;
UPDATE `yourtable` SET `newdate` = STR_TO_DATE(`olddate`, '%Y/%m/%d');

Just change "yourtable", "newdate", and "olddate" to your table's name, the new date column name, and the old datestamp column names respectively.

If you can't change the table structure, you could use something like the following:

WHERE year = '2011'
AND ((month = '05' AND day >= 21) OR (month = '06' AND day <= '15'))

(At least, I think that query does what you want in your specific case. But for e.g. a longer span of time, you'd have to think about the query again, and I suspect queries like this could become a pain to maintain)

UPDATE for the updated requirement

The principle remains the same, only the query becomes more complex. For the range of 2009/08/20 to 2011/04/15 it might look like this:

WHERE year = '2009' AND (month = '08' AND day >= '20' OR month BETWEEN '09' AND '12')
   OR year = '2010'
   OR year = '2011' AND (month BETWEEN '01' AND '03' OR month = '04' AND day <= '15')

where year = 2011 and (month between 5 and 6) and (day > 20 or day < 16)

You where seperating days and month whereas you must keep them together

parentheses must be set ...

Mike

It is important that you use OR otherwise it is nonsense