DISTINCT COUNT with GROUP BY query is too slow despite indexes I have the following query that counts the number of vessels in each zone for each week:

SELECT zone, DATE_FORMAT(creation_date, '%Y%u') AS date, COUNT(DISTINCT vessel_imo) AS vessel_count FROM vessel_position
WHERE zone IS NOT NULL
AND creation_date &gt;= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY zone, date;


The table has about 40 million rows. The execution plan for this is:

+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
| id | select_type | table           | partitions | type  | possible_keys      | key  | key_len | ref  | rows     | filtered | Extra                                    |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
|  1 | SIMPLE      | vessel_position | NULL       | range | creation_date,zone | zone | 5       | NULL | 21190904 |    50.00 | Using where; Using index; Using filesort |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+


Columns vessel_imo, zone and creation_date each indexed. Primary key is the composite key (vessel_imo, creation_date).

When I look at the query profile, I can see that a large amount of time i spent doing Creating sort index.

Is there anything I can do to improve this query further?
You'll need no three separate indexes, but one combined (covering) index on  creation_date, zone, vessel_imo (in this column order)

I don't know, how exactly MySQL works on this point, but it could help, when you would group by creation_date instead of the (calculated) date. On the other hand, when creation date contains the time and you do not output the time, then you have to group by by the (calculated) date.