mysql Search range by year-CodePudding

I want to get the date of birth of a cat as a range of years.

The year range is as follows, and several selections are possible.

Year : [2000, 2010, 2020]

If I select 2020, the period from 2020-01-01 to 2029-12-31.

If I select 2000, 2020, the period from 2000-01-01 to 2009-12-31 and 2020-01-01 to 2029-12-31.

<TABLE>
CAT {
 ID number,
 Birth DateTime,
...
}

I have searched for various ways through books and Google, but I can't find the way I want to do so..

select * from CAT 
where   birth between '2000-01-01' and '2009-12-31'
or      birth between '2010-01-01' and '2019-12-31'
or      birth between '2020-01-01' and '2029-12-31'

I tried to use 'Between' or '-01-01', but if [2000, 2020] is selected, it must be connected with 'or'. The more or, the slower the speed.

Please tell me a good way to do range calculations being able to use index. The index is being used for BirthDate.

Add) In my db, the query of 'SUBSTRING(YEAR(CAT.birth),1,3) IN (200,202)' works quickly. I have 500,000 data, can I use it like this?

CodePudding user response：

All you need to do is add index to column birth and run your query above with BETWEEN and OR.