Help optimizing query
Posted: Thu May 19, 2005 10:33 am
Hi everyone,
I've got a killer of a query here that is taking about 37 seconds to return results. I was wondering if anyone knew of any way I could make it quicker.
Here's the query:
And the relevant table structure
Basically, I want to get a count of all the time spent in each assigned category, then return the top 10 most used categories. Here's the type of results I want:
Initially, I thought it may have been all the summing I was doing, but taking out that condition didn't speed up the query at all.
Thanks for any and all help!
[Edit:] I should add that both the followups and assigned_challenge_categories tables have about 8000 rows each, while challenge_categories has 88
I've got a killer of a query here that is taking about 37 seconds to return results. I was wondering if anyone knew of any way I could make it quicker.
Here's the query:
Code: Select all
SELECT
cc.category,
(sum(hrs_spent) + floor(sum(min_spent)/60)) as hrs
FROM
assigned_challenge_categories as acc,
challenge_categories as cc,
followups as f
WHERE
cc.id = acc.category AND
f.challenge_id = acc.challenge_id
GROUP BY
category
ORDER BY
hrs DESC
LIMIT
10Code: Select all
desc assigned_challenge_categories;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| challenge_id | int(11) | | | 0 | |
| followup_id | int(11) | | | 0 | |
| category | int(11) | | | 0 | |
+--------------+---------+------+-----+---------+----------------+
desc challenge_categories;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| category | varchar(255) | | | | |
+----------+--------------+------+-----+---------+-------+
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| challenge_id | int(11) | | | 0 | |
| date | int(11) | | | 0 | |
| author | varchar(15) | | MUL | | |
| contents | text | YES | | NULL | |
| hrs_spent | tinyint(3) | YES | | NULL | |
| min_spent | tinyint(3) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+Code: Select all
+------------------------------------+-------+
| category | hrs |
+------------------------------------+-------+
| Dept | 75068 |
| Dept-Task | 72023 |
| Software-Server-Programming | 13114 |
| Software-Server-Operating System | 1732 |
| Default | 1694 |
| Hardware-PC | 1564 |
| Software-Server | 1332 |
| Software-Website Programming | 1129 |
| Hardware-Audio/Visual | 1107 |
| Software-Workstation-Configuration | 1060 |
+------------------------------------+-------+Thanks for any and all help!
[Edit:] I should add that both the followups and assigned_challenge_categories tables have about 8000 rows each, while challenge_categories has 88