2
\$\begingroup\$

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 >= 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?

\$\endgroup\$
  • \$\begingroup\$ The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How to Ask for examples, and revise the title accordingly. \$\endgroup\$ – 409_Conflict May 25 '18 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.