MySQL query speed issues when counting from second table

Question

So I'm having serious speed problems using a left join to count ticket comments from another table. I've tried using a sub-select in the count field and had precisely the same performance.

With the count, the query takes about 1 second on maybe 30 tickets, and 5 seconds for 19000 tickets (I have both a production and a development server so times are skewed a tad). I'm trying to optimize this as four variations of the query need to be run each time per page refresh.

Without the count, I see execution time fall from 1 second to 0.03 seconds, so certainly this count is being run across all tickets and not just the ones which are selected.

Here's a trimmed down version of the query in question:

            SELECT  tickets.ticket_id,
                            ticket_severity,
                            ticket_short_description,
                            ticket_full_description,
                            count(*) as commentCount,

            FROM    tickets (LEFT JOIN tickets_comment on ticket_id = tickets_comment.ticket_id)

            WHERE   ticket_status='Open'
              and   ticket_owner_id='133783475'

            GROUP BY
                    everything,
                    under,
                    the,
                    sun

Now, not all tickets have comments, so I can't just do a right or standard join. When doing that the speed is fairly good (1/10th the current), but any tickets without comments aren't included.

I see three fixes for this, and would like any and all advice you have.

1. Create a new column comment_count and use a trigger/update query on new comment
2. Work with the UI and grab comments on the fly (not really wanted)
3. Hope stackoverflow folks have a more elegant solution :þ

Ideas?

Answer 1

1. Is by far the fastest solution and you'll see it done in Rails all the time because it really is that fast.

2. count(*) is really only used when you aren't selecting any other attributes. Try count(ticket_id) and see if that helps. I can't run explain so I can't test it myself but if your analysis is correct it should help.

Answer 2

Try running explain on the query to make sure the correct indexes are being used. If there are no indexes being used, create another one

Answer 3

A co-worker has come to the rescue. The query was just using join improperly.

What must be done here is create a second table with a query like:

select count(*) from tickets_comment group by ticket_id where (clause matches other)

which will create a table with counts for each ticket id. Then join that table with the ticket table where the ticket ids match. It's not as wicked fast as creating a new column, but it's at least 1/10th the speed it was, so I'm pleased as punch.

Last step is converting nulls (on tickets where there were no comments) into zeros