'mysql Search range by year

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?

mysqlperformancedatesearch


Solution 1:[1]

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

Solution 2:[2]

if you are using mysql, did you tried with YEAR() function ?

Example: SELECT * FROM cat WHERE YEAR(birth) BETWEEN 1990 AND 2018 ORDER BY YEAR(birth) ASC;

Please check this Mysql YEAR()

Solution 3:[3]

If you expect to get more than about 20% of the rows from a table, then an INDEX will be eschewed for simply scanning all the rows.

Otherwise, having INDEX(birth) will help with certain queries, but none of the ones mentioned so far. Each of them is not "sargable" .

To use the index (and be efficient for a limited range of years or date range), something like this is probably what you need. This example covers 2 calendar years.

WHERE CAT.birth >= '2018-01-01'
  AND CAT.birth  < '2020-01-01'

BTW: SUBSTRING(YEAR(CAT.birth),1,3) can be simplified to LEFT(CAT.birth, 3), but that still cannot use the recommended index.

BTW: A 'bug' in your code: Since birth is a DATETIME, and '2009-12-31' excludes but midnight of the morning of New Year's Eve. Note how I avoided this common 'bug' by using < and the next day. This works whether you have DATE, DATETIME or DATETIME(6), etc.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 bubbak
Solution 2 DharmanBot
Solution 3

Related Questions