YEAR, MONTH, DAY functions usage kill mysql performance and do not use indexes

[arekm]

Usage of YEAR(date), MONTH()... in this code causes huge overload of mysql databases (if history table is huge) because mysql is unable to use indexes for that.

Please never use YEAR, MONTH, DAY functions in queries.

Use date >= rangeX AND date <= rangeY (or just date_year = XYZ if using fields like: date_year int(11) GENERATED ALWAYS AS (year(date)) STORED,).

I'm looking at one system where:

select DAY(date) as GId,  count(id) as NbPages, count(distinct IP) as NbIP,
            count(image_id) as NbImg, count(distinct category_id) as NbCat , MaxPages, MaxIP, MaxImg from piwiht_history , (select max(n.MaxPages) as MaxPages, max(n.MaxIP) as MaxIP, max(n.MaxImg) as MaxImg
        from (select DAY(date) as GId, count(id) as MaxPages, count(distinct IP) as MaxIP, count(image_id) as MaxImg
            from piwiht_history where YEAR(date) = 2026 and MONTH(date) = 3  AND  NOT ( IP LIKE 
[... and here hundreds of IP LIKE xyz ]
) group by GId  order by GId asc

that takes 10 minutes to execute for table with

11:23:59 mysql{22}> select count(*) from piwiht_history;
+----------+
| count(*) |
+----------+
|  1000504 |
+----------+
1 row in set (0.30 sec)

records.