MySQL select records between day of the year and year

Question

I have a table where I insert the year, month number, week number of the year, and day number of the year with each record.

I'm using those fields to filter records instead of full date because I have millions of records, and I want to increase the performance by comparing integers instead of dates.

I was able to select records between dates based the day number and year. The query works well if the years are the same, but once I change the year the query doesn't work because I'm using AND.

Table looks like this:

• ID
• Day
• Week
• Month
• Year
• Full Date

Here is the working query

SELECT COUNT(id) AS records_count
FROM table1
WHERE table1.day >= 176
AND table1.day <= 275
AND table1.year <= 2015
AND table1.year >= 2015

And this is the query that I need to adjust

SELECT COUNT(id) AS records_count
FROM table1
WHERE table1.day >= 275
AND table1.day <= 176
AND table1.year <= 2014
AND table1.year >= 2015

Answer 1

SELECT COUNT(id) AS records_count
FROM table1
WHERE (year = 2014 and day >= 275)
   OR (year = 2015 and day <= 176)

And as baao commented - an indexed date column is super fast and easier to query.

Answer 2

Your method of storing the dates is exactly wrong for what you want to do. The general form would be:

where (year = 2014 and day >= 275 or year > 2014) and
      (year = 2015 and day <= 176 or year < 2015)

This will work for any pair of years, not just those that are one year apart.

Now, if you stored the dates normally, then you would simply do:

where date >= makedate(2014, 275) and date <= makedate(2015, 176)

What is really, really nice about this structure is that MySQL can use an index on date. That isn't possible with your query.

In general, a micro-optimization such as using integers instead of some other data type is not worth the effort in relational databases. Usually, the cost of reading the data is much more expensive than processing within a row. And, in fact, this example is a great example of it. Why try to increase the speed of comparisons when you can remove the need for them entirely using an index?

Answer 3

Generalized solution (can be from year 2000 to 2020 for example)

SELECT COUNT(id) AS cnt
FROM tbl
WHERE ((year > under_min_year and year < above_max_year)
      and ((year <> min_year and year <> max_year)
           or (year = min_year and day > 265)
           or (year = max_year and day < 300)))

Answer 4

The problem is that you want to count the records that have the year smaller or equal than 2014 AND the same year bigger or equal than 2015. There's no number smaller or equal than 2014 and in the same time bigger or equal than 2015.