I have a table called ticket_log
which has millions of records in it. Each ticket log is for a ticket, so the ticket_log table has a ticket_id
column. I need to find out which ticket has the maximum number of logs.
If the table had only a couple of thousand entries, then the following query would have easily worked -
select ticket_id, count(ticket_log_id) as myCount
from ticket_log
group by ticket_id
order by myCount desc limit 1
However, when I try running this on a table that has millions of records in it, the query takes forever. Some optimization techniques suggest that we can add a filter of sorts to the query like where ticket_created > '2014'
for example, but that is not an option.
Given this scenario, how can the query be optimized for a very large number of records?
Update: the query took slightly over an hour to run for the table with millions of records.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `ticket_log` ADD INDEX `ticket_log_idx_ticket_id` (`ticket_id`);
SELECT
ticket_log.ticket_id,
count(ticket_log.ticket_log_id) AS myCount
FROM
ticket_log
GROUP BY
ticket_log.ticket_id
ORDER BY
myCount DESC LIMIT 1