SELECT with WHERE and GROUP BY -- can it go any faster?
I have the following table: CREATE TABLE history ( id int(10) unsigned NOT NULL auto_increment, time_sec int(10) unsigned NOT NULL default '0', account_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY account_id (account_id), KEY time_sec (time_sec), ) TYPE=MyISAM AUTO_INCREMENT=36653 ; I need to know which months have activity for any given account_id. Here is my query (which does give me exactly the data I need, albeit slowly): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.1818 sec) -- The problem is speed. 0.1818 seconds to fetch six rows is too slow. The account in question has about 6000 rows. And there are a total of about 25000 rows in the table. I've at least figured out that the thing that there are two things slowing this down: the WHERE and GROUP BY. E.g., if I drop the WHERE clause (fetch rows for all accounts): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 14 (15 total, Query took 0.0930 sec) -- ...it's about twice as fast (or half as slow :-) And If I drop the GROUP BY, e.g.: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history ORDER BY history.time_sec DESC Showing rows 0 - 29 (23895 total, Query took 0.0008 sec) -- Then it's very fast. Do you guys—and girls!—have any suggestions about how to speed this up, besides adding indices on time_sec and account_id (which I've done)? Thanks for taking a look. ...Rene
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
René Fournier [EMAIL PROTECTED] wrote on 11/17/2005 04:19:25 PM: OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output = = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
On 17-Nov-05, at 2:41 PM, [EMAIL PROTECTED] wrote: René Fournier [EMAIL PROTECTED] wrote on 11/17/2005 04:19:25 PM: OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) - --- -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) - --- -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output = = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Well, I have a number of queries that are executed on every page, and I'm just trying to optimize them. I don't presume to be a DBA, but I would like to learn how to tune these queries as much as possible... Thanks for your response. Maybe 0.0267 seconds is as good as it gets. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
