I've got a slow query (about 6-10 minutes) that I've been trying to speed up and I'd really appreciate any pointers. I've followed various guides to creating indexes based on cardinality of columns but I've run out of ideas.
The OS is Windows Server 2016 Standard on a server with dual Xeon E5-2620 v3 @ 2.4Ghz, 30 GB of RAM, which is a test environment and could be beefed up. I'm not sure about the storage - all I know is it's a VMWare virtual SCSI drive.
I'm Running MySQL 8.0.12 Community.
My create table code is:
CREATE TABLE `cps_dispensing_data_summary`
(
`year` INT(4) DEFAULT NULL,
`month` INT(2) NOT NULL,
`dispenser` VARCHAR(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`scan_ref` VARCHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`pi_drug_formulation` VARCHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`line_no` INT(10) DEFAULT NULL,
`ppd_code` MEDIUMINT(6) UNSIGNED ZEROFILL DEFAULT NULL,
`qty` DECIMAL(10,2) DEFAULT NULL,
`packsize` DECIMAL(7,2) DEFAULT NULL,
`gic_for_71` DECIMAL(12,2) DEFAULT NULL,
`gic_for_no_71` DECIMAL(12,2) DEFAULT NULL,
`form_type_code` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`pd_form_barcode` VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
KEY `year_month_ppdcode_packsize_dispenser` (`year`,`month`,`ppd_code`,`packsize`,`dispenser`),
KEY `year_month_dispenser` (`year`,`month`,`dispenser`),
KEY `ppdcode_packsize_year_month` (`ppd_code`,`packsize`,`year`,`month`)
USING btree)
engine=innodb DEFAULT charset=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
The table contains about 212,000,000 rows, which is about 25GiB and the indexes are using about 25GiB.
The query I'm running is very long with about 1000 of these conditions
(data_summ.PPD_CODE = x AND data_summ.PACKSIZE = y) OR...
so I've truncated it here, but hopefully you get the idea:
SELECT PACKSIZE, YEAR, MONTH, SUM(GIC_FOR_NO_71) AS GIC_FOR_NO_71, SUM(QTY/PACKSIZE) AS PACKS
FROM cps_dispensing_data_summary data_summ
WHERE
YEAR = 2021 and MONTH = 5
AND
((data_summ.PPD_CODE = 520 AND data_summ.PACKSIZE = 300) OR
(data_summ.PPD_CODE = 530 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 531 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1310 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1312 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1313 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1314 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1500 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2000 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2010 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 2010 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2200 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2210 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2220 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2221 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2222 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2230 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 2240 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 2400 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2410 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 2700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2800 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 3100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 3110 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 3300 AND data_summ.PACKSIZE = 12) OR
(data_summ.PPD_CODE = 3310 AND data_summ.PACKSIZE = 5) OR
(data_summ.PPD_CODE = 3320 AND data_summ.PACKSIZE = 20) OR
(data_summ.PPD_CODE = 3320 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 4100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4110 AND data_summ.PACKSIZE = 70) OR
(data_summ.PPD_CODE = 4400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4410 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4730 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 4740 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 4910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4911 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5212 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6110 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6120 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6121 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9012 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 9013 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 9200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10730 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 10800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10820 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 10900 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 12100 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12100 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 12101 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12110 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12110 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 12111 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12112 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12120 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 14000 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14010 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14012 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14700 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 14900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 15800 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 15810 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 15812 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 17000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 20) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 20700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 20710 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 20712 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22720 AND data_summ.PACKSIZE = 32) OR
(data_summ.PPD_CODE = 22720 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22721 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 22730 AND data_summ.PACKSIZE = 32) OR
(data_summ.PPD_CODE = 22750 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22760 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 25750 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 27000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27012 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27200 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 28800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 28800 AND data_summ.PACKSIZE = 1000) OR
(data_summ.PPD_CODE = 29900 AND data_summ.PACKSIZE = 2000) OR
(data_summ.PPD_CODE = 32400 AND data_summ.PACKSIZE = 1000) OR
(data_summ.PPD_CODE = 32500 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33000 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33010 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33030 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33031 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33121 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33124 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33200 AND data_summ.PACKSIZE = 5) OR
(data_summ.PPD_CODE = 33300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33310 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 34000 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 34010 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 34012 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 34100 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 34101 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34110 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 34120 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34121 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34130 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34150 AND data_summ.PACKSIZE = 2) OR
(data_summ.PPD_CODE = 34300 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 34400 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 34410 AND data_summ.PACKSIZE = 90) OR
(data_summ.PPD_CODE = 35900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 35910 AND data_summ.PACKSIZE = 150) OR
(data_summ.PPD_CODE = 36000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36212 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36310 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36400 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36410 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36412 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36600 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 36610 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36700 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 36710 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 36712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36713 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 36900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37100 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37110 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37200 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 37210 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 37400 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37410 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37412 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37600 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37610 AND data_summ.PACKSIZE = 6) OR
(data_summ.PPD_CODE = 37610 AND data_summ.PACKSIZE = 14) OR
(data_summ.PPD_CODE = 37700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37800 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37810 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 38000 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39300 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39400 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39410 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39500 AND data_summ.PACKSIZE = 8) OR
(data_summ.PPD_CODE = 39500 AND data_summ.PACKSIZE = 50) OR
(data_summ.PPD_CODE = 39510 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39800 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39810 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39812 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40800 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 41200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41820 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 43400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44010 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 44400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44410 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46911 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 47100 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 47120 AND data_summ.PACKSIZE = 4) OR
(data_summ.PPD_CODE = 49400 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 49410 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 49800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 49810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 49812 AND data_summ.PACKSIZE = 28))
GROUP BY PPD_CODE, PACKSIZE
The explain output for the full query is:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
'1' | 'SIMPLE' | 'data_summ' | NULL | 'range' | 'year_month_ppdcode_packsize_dispenser,year_month_dispenser,ppdcode_packsize_year_month' | 'ppdcode_packsize_year_month' | '18' | NULL | '84714' | '100.00' | 'Using index condition' |
And my slow query log:
# Time: 2021-09-16T16:26:32.927850Z
# Query_time: 386.492964 Lock_time: 0.014415 Rows_sent: 962 Rows_examined: 5898140
SET timestamp=1631809592;
Do you think my indexes or table structure could be better or maybe it's a hardware issue?
PPD_CODE
,PACKSIZE
into a (temporary) table and joining it with the original (after creating correct indexes, obviously).