MYSQL Query Index Check

Question

I have a table with the following structure;

CREATE TABLE `operations_copy` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `zoneId` int(11) unsigned DEFAULT NULL,
  `machineId` int(11) unsigned DEFAULT NULL,
  `shiftId` int(11) unsigned DEFAULT NULL,
  `statusId` int(11) unsigned DEFAULT NULL,
  `actualCycle` float(10,4) NOT NULL,
  `targetCycle` float(10,4) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=185538661 DEFAULT CHARSET=latin1;

I have nearly 200m records in this table.

I am frequently performing the following queries on this table;

1 - Operations By Zone

SELECT zoneId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') 
GROUP BY `zoneId`,`statusId` 
ORDER BY NULL;

2 - Operations By Machine

SELECT machineId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId` 
ORDER BY NULL;

3 - Operations By Shift

SELECT shiftId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') 
GROUP BY `shiftId`,`statusId` 
ORDER BY NULL;

I am currently looking at 30s for query #1.

I have enabled caching, which reduces the query time dramatically after the initial query, but I still want to optimise the initial query.

Studying has shown me that I need to make better use of indexes (ref: https://www.slideshare.net/billkarwin/how-to-design-indexes-really, MYSQL docs and more). So I want to check the following indexes will be the best to optimise each query;

#1

KEY `operations_by_zone_query` (`date`,`zoneId`,`statusId`,`id`)

#2

KEY `operations_by_machine_query` (`date`,`machineId`,`statusId`,`id`)

#3

KEY `operations_by_shift_query` (`date`,`shiftId`,`statusId`,`id`)

Any help will be much appreciated. Thanks.

Answer 1

I have an interesting idea for you.

Create the following indexes

ALTER TABLE operations
    ADD INDEX `date_zoneId_statusId_ndx`    (`date`,`zoneId`,`statusId`)
   ,ADD INDEX `date_machineId_statusId_ndx` (`date`,`machineId`,`statusId`)
   ,ADD INDEX `date_shiftId_statusId_ndx`   (`date`,`shiftId`,`statusId`)
   ,ADD INDEX `zoneId_statusId_date_ndx`    (`zoneId`,`statusId`,`date`)
   ,ADD INDEX `machineId_statusId_date_ndx` (`machineId`,`statusId`,`date`)
   ,ADD INDEX `shiftId_statusId_date_ndx`   (`shiftId`,`statusId`,`date`)
;

You could then evaluate your queries by running:

EXPLAIN SELECT zoneId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') 
GROUP BY `zoneId`,`statusId` 
ORDER BY NULL\G

EXPLAIN SELECT machineId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId` 
ORDER BY NULL\G

EXPLAIN SELECT shiftId, statusId, COUNT(*) as intervals 
FROM `operations` 
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') 
GROUP BY `shiftId`,`statusId` 
ORDER BY NULL\G

The output from each EXPLAIN will plainly tell you which index.

It will also show the least number of steps the Query Optimizer will take.

My guess is that the indexes with the date column last will be selected. An exception may be a small date range which may favor the indexes with the date column first. In that instance, you would want to keep all 6 indexes.

The indexes with the date column last would be used by the GROUP BY so that no temp table sorting would be necessary.

WARNING ON HAVE MANY INDEXES : Bulk INSERT performance would suffer due to managing multiple covering indexes, but SELECT performance would improve greatly. Dropping unnecessary indexes becomes a necessity.

Answer 2

Would you prefer 3 seconds instead of 30? Or maybe even 0.3, if the planets are aligned right.

Build and maintain a "Summary table". The perform a similar query against the summary table. It is likely to be 10 times as fast. (And you won't need the 3 or 6 indexes on the big table.) MOre info: http://mysql.rjweb.org/doc.php/summarytables

It may be practical to have a single Summary Table:

date,   -- not datetime
machineId,
statusId,
shifted,
subtotal,   -- from COUNT(*) for the combo
PRIMARY KEY(one of the needed indexes),
plus the other two indexes needed

The summarization could be done every night (necessitating an index starting with date in the main table) or done in realtime via a IODKU. (Performance may dictate one versus the other.)

Side notes:

• (m,n) on FLOAT is wasteful. It leads to an extra rounding. Either go with plain FLOAT (4 bytes, but only about 7 significant digits) or plain DOUBLE (8 bytes and 16 significant digits or DECIMAL(10,4) (5 bytes) or maybe a smaller DECIMAL.
• Consider shrinking the ids from 4-byte to something smaller. This would shrink the table, thereby helping speed slightly. And it would shrink the tables of machines, shifts, and statuses. And it would shrink the recommended indexes (that I want you not to add). And it would help the summary table(s) (that I do want you to add).
• No, I don't know which set of 3 indexes (see Rolando's Answer) is better.
• date > 'XXXX-XX-XX' AND date <= 'XXXX-XX-XX' -- Midnight usually belongs with the starting date, not the ending one. That is, I would expect to see date >= 'XXXX-XX-XX' AND date < 'XXXX-XX-XX'