MySQL query optimization using GROUP BY functions in time

Question

I have the following query:

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)

Initially, I had indexes on separate columns, such as jobid and start, but quickly realized that MySQL really distinguishes one index per table in select. That way, it will use the jobid index and then do a fairly large scan to filter out the original range.

Adding an index (jobid, start) helped a bit, but GROUP BY still causes performance issues. I read the docs in GROUP BY optimizations 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:

• Will the optimization group work with time functions (YEAR, MONTH, DAY, etc.)? Or will I have to store these values ​​as separate columns? The reason I like to perform functions is because it means that I can control the time zone based on each connection and get results adapted to the time zone of the end users. If I need to pre-store the year, month and day, I will do it through UTC, and then all my users will receive reports only in UTC format.

• №1, ? (jobid, start) WHERE, GROUP BY , (, , ) , # 1 (, , , , ). , , , WHERE GROUP , . : , , ?

, , . , /, :

• , .
• , - RRDtool, , , .
• , , , , , , .

, - , - , !

---

: , :

:

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   

:

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

Answer 1

jobid, start, location, step

:

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 YEAR(start), MONTH(start), DAY(start), location, step
ORDER BY location, step, YEAR(start), MONTH(start), DAY(start)

, MySql , YEAR, MONTH DAY.

• WHERE - using filesort
• 3 : y = YEAR(start), m = MONTH(start), d=DAY(start), jobid, y, m, d, location, step WHERE ... AND y = 2010 AND m = 12 AND d BETWEEN 1 AND 08 using temporary using filesort.

3 , GROUP BY , .

Answer 2

GROUP BY location, step, YEAR(start), MONTH(start), DAY(start)
ORDER BY location, step, YEAR(start), MONTH(start), DAY(start)

GROUP BY location, step, date_format(start, '%Y%m%d')
ORDER BY location, step, date_format(start, '%Y%m%d')

Answer 3

, , , , (, , )

. jobid + start + location + step, -, BETWEEN. WHERE - GROUP BY, , , - jobid + start index.

, imho, . : .

Answer 4

, , , .

First, the query will be grouped by integer data, which will compare much faster. Secondly, it is possible that the database engine can automatically index these numbers.

I would also think about unloading jobid into a separate table if the value repeats.