MySQL query performance on single table

Question

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?

Consider loading those PPD_CODE, PACKSIZE into a (temporary) table and joining it with the original (after creating correct indexes, obviously). — mustaccio, Sep 16, 2021 at 17:25
@Jeremy Fox Could you post TEXT results of SHOW EXTENDED INDEX FROM cps_dispensing_data_summary; so we can all see the automatically created Primary Key being used behind the scenes? Normally a PK is very brief and could be a simple AUTO INCREMENTED number that assists with minimizing space required for your defined INDEXES you know you need. Welcome to dba.stackexchange.com — Wilson Hauck, Sep 16, 2021 at 23:57
@Wilson Hauck thank you. I'm not sure how to post TEXT results of a query. I can tell you it's 16 columns though - for some reason (or maybe no reason) I didn't define a primary key for this table. — Jeremy Fox, Sep 17, 2021 at 8:56
@mustaccio - that seems like a popular recommendation. I'll let you know how it goes. — Jeremy Fox, Sep 17, 2021 at 8:58
If you can not post TEXT results from SHOW EXTENED ... , post the image, we will try to read it for content. No Primary Key caused bloated index sizes. — Wilson Hauck, Sep 17, 2021 at 13:19

Gerard H. Pille · Accepted Answer · 2021-09-16 21:20:35Z

0

Have an index on year, month, packsize and ppd_code.

Then change your query so that a given packsize is only specified once, as in

OR ( data_summ.PACKSIZE = 28 and data_summ.PPD_CODE in (
530, 531, 1100, 1300, 1500, 2220, 2221, 2222, 2700, 2710, 2712, 3100,
3110, 4100, 4400, 4410, 4700, 4710, 4712, 4910, 4911, 5200, 5210, 5212,
5400, 6100, 6110, 6120, 6121, 9000, 9010, 9200, 9900, 9910, 10700,
10710, 10712, 10800, 10810, 14900, 17000, 20700, 22721, 27000, 27010,
27012, 28800, 32500, 33100, 33300, 33310, 35900, 36000, 36010, 36210,
36212, 36300, 36310, 36610, 36712, 36900, 36910, 37600, 37700, 37710,
37712, 37810, 39200, 39210, 39510, 39900, 39910, 40000, 40010, 41200,
41210, 41800, 41810, 43400, 44000, 44400, 44410, 46900, 46910, 46911,
49800, 49810, 49812))

answered Sep 16, 2021 at 21:20

Gerard H. Pille

3,2351 gold badge8 silver badges13 bronze badges

Thanks @Gerard H. Pille. This worked very well - my query time is now 46 seconds, which is ok.
– Jeremy Fox
Oct 5, 2021 at 14:08
It took you a while! ;-)
– Gerard H. Pille
Oct 5, 2021 at 14:10
Ha! I'm a very busy man.
– Jeremy Fox
Oct 6, 2021 at 10:42

Add a comment |

Rick James · Accepted Answer · 2021-09-16 21:32:02Z

Heads up:

----- 2019-07-22 8.0.17 General Availability -- -- -----

The ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. Support for ZEROFILL and display widths for integer data types will be removed in a future MySQL version. Consider using an alternative means of producing the effect of these attributes. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.

All though the Explain seems to indicate that it used a useful index, it did touch 6M rows, finding less than 1K rows. So, I don't think the index can be improved.

Another approach is to put the 1K ppdcode-packsize pairs in a temp table, then JOIN to the main table to locate the desired rows.

What is the value of innodb_buffer_pool_size? I assume about 22G.

Were the rows inserted (roughly) chronologically? That would imply that the May '21 rows are 'clustered'. However, the index picked may have to use lots of rows.

HDD drives or SSD?

Thanks for the heads-up. The PPD_CODE was stored as a VARCHAR (which always contained six characters) so I changed it to MEDIUMINT to some space. I'll remove the ZEROFILL. The innodb_buffer_pool_size is 16G - there are some other services running so I didn't want to use too much. Yes, rows are inserted chronologically in batches each month. Not sure what type of storage it is but I'm guessing it's HDD (for a cheapish test environment). Someone else commented that a temporary table might help - I'll give that a try. — Jeremy Fox, Sep 17, 2021 at 8:34
@JeremyFox - As for saving space, be aware that the (2) in INT(2) means nothing -- it takes 4 bytes. See TINYINT (1 byte) for month. Etc. — Rick James, Sep 17, 2021 at 15:55

Akina · Accepted Answer · 2021-09-17 08:56:24Z

Use this form of the conditional expression:

...
WHERE (data_summ.PPD_CODE, data_summ.PACKSIZE)
   IN ( (520, 300), (530, 28), (531, 28), ... )

This expression matches the prefix of the ppdcode_packsize_year_month index expression.

If the amount of pairs is really high then save these pairs into temporary table indexed by the same expression and join to the data table:

CREATE TEMPORARY TABLE filter ( PPD_CODE MEDIUMINT(6), -- datatype must match
                                PACKSIZE DECIMAL(7,2), 
                                PRIMARY KEY (PPD_CODE, PACKSIZE) ) ENGINE = Memory;
INSERT IGNORE INTO filter VALUES
    (520, 300), (530, 28), (531, 28), ... ;

and then

SELECT ...
FROM cps_dispensing_data_summary
NATURAL JOIN filter -- provides the filtering instead of long WHERE
WHERE `YEAR` = 2021 and `MONTH` = 5
GROUP BY PPD_CODE, PACKSIZE;

Or maybe include YEAR and MONTH columns in this table also and create an index which strictly matches the expression of ppdcode_packsize_year_month index.

PS. It is strange that you use PPD_CODE in grouping expression but do not include it into the output list...

PPS. You use INT(4) for year column whereas YEAR datatype exists (and it is more compact - 1 byte instead of 4).

UPDATE

The values for year and month in WHERE are single constants. So you may increase your query speed without great edition by the next 2 modifications:

Create an index by (`year`, `month`, `ppd_code`,`packsize`).
Add year and month to GROUP BY expression.

This will decrease the amount of the index readings.

Thanks @Akina - I'll give this a try. Is the INSERT IGNORE to prevent PK duplicate errors stopping the transaction? Apologies, I made an error in my original post, PPD_CODE should have been in the output list. — Jeremy Fox, Sep 17, 2021 at 8:43
@JeremyFox Is the INSERT IGNORE to prevent PK duplicate errors stopping the transaction? IGNORE keyword converts duplicate violation error (which aborts the query) to warning, and only unique pairs are inserted (and obviously it makes no sense to have dups in WHERE condition). — Akina, Sep 17, 2021 at 8:47

Stack Exchange Network

MySQL query performance on single table

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
query-performance
index-tuning
or ask your own question.

Hot Network Questions

MySQL query performance on single table

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlquery-performanceindex-tuning or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
query-performance
index-tuning
or ask your own question.