StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimizing MySQL query using GROUP BY on time functions
primarykey
Id
4506431
data
AcceptedAnswerId
4506722
AnswerCount
4
ClosedDate
CommentCount
5
CommunityOwnedDate
CreationDate
2010-12-22T05:27:57.303
FavoriteCount
1
LastActivityDate
2011-02-15T02:00:41.557
LastEditDate
2010-12-22T06:26:11.853
LastEditorUserId
62187
OwnerUserId
62187
ParentId
0
PostTypeId
1
Score
3
ViewCount
2120
LastEditorDisplayName
text
Body
I have the following query: <pre><code>SELECT location, step, COUNT(*), AVG(foo), YEAR(start), MONTH(start), DAY(start) FROM table WHERE jobid = 'xxx' AND start BETWEEEN '2010-01-01' AND '2010-01-08' GROUP BY location, step, YEAR(start), MONTH(start), DAY(start) </code></pre> Originally I had indexes on individual columns, such as jobid and start, but quickly realized that MySQL only really honors one index per table in a select. As such, it would use the jobid index and then do a pretty large scan to filter out by the start range. Adding an index on (jobid, start) helped quite a bit, but the GROUP BY is still causing performance issues. I've read the <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html" rel="nofollow">docs on GROUP BY optimizations</a> and understand that in order to benefit from these optimizations I need an index that contains (location, step, start), but I still have two open questions: <ol> <li>Will the group by optimizations even work with the time functions (YEAR, MONTH, DAY, etc)? Or am I going to have to store these values as separate columns? The reason I like doing the functions is that it means I can control the time zone on a per-connection basis and get back results tailored to the end-users time zone. If I have to pre-store the year, month, and day, I'll do it via UTC and then all my users will just get reports in UTC.</li> <li>Even if I can solve issue #1, can I even do this? The index (jobid, start) helped with the WHERE clause, but the GROUP BY needs a different index to be optimized (location, step, start) or, depending on the answer to #1, (location, step, year, month, day). But the problem is that those two indexes don't share a common left-hand set of columns, so I don't believe my WHERE and GROUP by can be compatible such that the same index gets used. So my question is: am I just hosed here?</li> </ol> Any other thoughts on how to achieve this would be helpful. And, just to preempt a few questions/comments that might come up: <ol> <li>Yes, this is a time-series data set.</li> <li>Yes, it would benefit from something like <a href="http://www.mrtg.org/rrdtool/" rel="nofollow">RRDtool</a>, but doing so would cause me to loose doing timezone-specific results.</li> <li>Yes, pre-calculating rollups would probably be a good idea, but I don't need awesome performance and so I'm OK with good performance if it lets me customize the results for each user's timezone.</li> </ol> With the above said, if anyone has any design suggestions on how to do something like rollups or round-robin databases and still get timezone-specific results, I'm all ears! <hr> Update: as requested, here is some more info: show indexes from output: <pre> step 0 PRIMARY 1 step_id A 16 NULL NULL BTREE step 1 start 1 start A 16 NULL NULL BTREE step 1 step 1 step A 2 NULL NULL BTREE step 1 foo 1 foo A 16 NULL NULL YES BTREE step 1 location 1 location A 2 NULL NULL YES BTREE step 1 jobid 1 jobid A 2 NULL NULL YES BTREE </pre> show create table output: <pre> CREATE TABLE `step` ( `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `step` smallint(2) unsigned NOT NULL, `step_id` int(8) unsigned NOT NULL AUTO_INCREMENT, `location` varchar(12) DEFAULT NULL, `jobid` varchar(37) DEFAULT NULL, PRIMARY KEY (`step_id`), KEY `start_time` (`start`), KEY `step` (`step`), KEY `location` (`location`), KEY `job_id` (`jobid`) ) ENGINE=InnoDB AUTO_INCREMENT=240 DEFAULT CHARSET=utf8 </pre>
Tags
<mysql><database-design><optimization><query-optimization>
Title
Optimizing MySQL query using GROUP BY on time functions
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USPatrick Lightbody
UserOwnerUserId
1. USPatrick Lightbody
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POOptimizing MySQL query using GROUP BY on time functions
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POOptimizing MySQL query using GROUP BY on time functions
 UserUserId
 USshantanuo
 VoteTypeVoteTypeId
 VTFavorite
3. VO
 singulars
 PostPostId
 POOptimizing MySQL query using GROUP BY on time functions
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.