0

I have a MariaDB (MySQL) query that uses a DISTINCT to get my results. The query takes about 6 seconds to complete.

Is there any way to improve the speed of the `"Copying to tmp table" step? This is where the majority of the running time comes from.

A few things to note:

  • The temp table is created in-memory (not on disk)
  • I'd like to see if this can be improved without re-writing the query
  • fact_warehouse_inventory_change has ~48K rows
  • dim_date has ~9K rows starting from 01-01-1995 through 2020-12-31

QUERY

SELECT SQL_NO_CACHE DISTINCT fact_warehouse_inventory_change.warehouse_location_id
FROM fact_warehouse_inventory_change
INNER JOIN dim_date ON
    dim_date.date >= fact_warehouse_inventory_change.row_effective_date AND
    dim_date.date <= fact_warehouse_inventory_change.row_expiration_date

EXPLAIN

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dim_date
         type: index
possible_keys: ux_date_date_id,date,date_desc
          key: date
      key_len: 3
          ref: NULL
         rows: 9498
     filtered: 100.00
        Extra: Using index; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: fact_warehouse_inventory_change
         type: ALL
possible_keys: row_effective_date,row_effective_date_row_expiration_date,row_expiration_date_row_effective_date_warehouse_location_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 48124
     filtered: 100.00
        Extra: Range checked for each record (index map: 0xC8)

TABLES

-- Abbreviated table definition
CREATE TABLE `dim_date` (
    `date_id` INT(11) NOT NULL,
    `date` DATE NOT NULL,
    `year` SMALLINT(6) NULL DEFAULT NULL,
    `year_for_week` SMALLINT(6) NULL DEFAULT NULL,
    `year_for_week_iso8601` SMALLINT(6) NULL DEFAULT NULL,
    `quarter` TINYINT(4) NULL DEFAULT NULL,
    `month` TINYINT(4) NULL DEFAULT NULL,
    `week_of_year` TINYINT(4) NULL DEFAULT NULL,
    `week_of_month` TINYINT(4) NULL DEFAULT NULL,
    `week_of_year_iso8601` TINYINT(4) NULL DEFAULT NULL,
    `week_of_month_iso8601` TINYINT(4) NULL DEFAULT NULL,
    `weekday_in_month` TINYINT(4) NULL DEFAULT NULL
    PRIMARY KEY (`date_id`, `date`),
    UNIQUE INDEX `ux_date_date_id` (`date`, `date_id`),
    UNIQUE INDEX `date` (`date`),
    UNIQUE INDEX `date_desc` (`date`)
)
COLLATE='utf8_general_ci'
/*!50100 PARTITION BY RANGE (year(date))
(PARTITION part0 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION part1 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION part2 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION part3 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION part4 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION part5 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION part6 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION part7 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION part8 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION part9 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION part10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)  */;

CREATE TABLE `fact_warehouse_inventory_change` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `warehouse_allocation_key` INT(11) NULL DEFAULT NULL,
    `date_id` INT(11) NOT NULL,
    `warehouse_item_id` INT(11) NOT NULL,
    `warehouse_location_id` INT(11) NOT NULL,
    `inventory_qty` INT(11) NULL DEFAULT NULL,
    `reorder_qty` INT(11) NULL DEFAULT NULL,
    `minimum_on_hand_qty` INT(11) NULL DEFAULT NULL,
    `individual_price` DECIMAL(10,2) NULL DEFAULT NULL,
    `unit_of_measure_price` DECIMAL(10,2) NULL DEFAULT NULL,
    `row_effective_date` DATE NOT NULL,
    `row_expiration_date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `date_id` (`date_id`),
    INDEX `warehouse_item_id` (`warehouse_item_id`),
    INDEX `row_effective_date` (`row_effective_date`),
    INDEX `warehouse_allocation_key` (`warehouse_allocation_key`),
    INDEX `warehouse_location_id` (`warehouse_location_id`),
    INDEX `row_effective_date_row_expiration_date` (`row_effective_date`, `row_expiration_date`),
    INDEX `row_expiration_date_row_effective_date_warehouse_location_id` (`row_expiration_date`, `row_effective_date`, `warehouse_location_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=47776
;

CONFIG

tmp_table_size                  = 256M
max_heap_table_size             = 256M
innodb_buffer_pool_size         = 4G
innodb_log_buffer_size          = 8M
innodb_log_files_in_group       = 2
innodb_file_per_table           = 1
innodb_open_files               = 400
innodb_io_capacity              = 400
2
  • The query is missing (after line 3) dim_date.date_id = ...date WHERE. Adding that will speed it up some. The following items assume that is present and critique the schema and query further...
  • That query is an example of why dates should not be normalized.
  • Do not redundantly specify UNIQUEness -- PK(a,b) + UNIQUE(b,a) -- change UNIQUE to INDEX.
  • There is no "DESC" index, so that second UNIQUE(date) is redundant. (8.0 will fix that deficiency; meanwhile most queries can run backwards efficiently.)
  • There was no advantage, and some performance disadvantages, in PARTITIONing dim_date.
  • There is a YEAR datatype.
  • Once you have thrown away dim_date completely, learn the functions needed for finding MONTH(), etc. And replace date_id with just date.
  • Well, OK, there is a use for dim_date -- whenever you need to report on the data and include dates for which there is no data. Still, date_id is useless, and date should be the PK.
  • fact_warehouse_inventory_change currently has under 50K rows; will it grow to millions? If so, we need to talk about creating and maintaining "Summary tables".
  • There at least 2 redundant indexes in fact_warehouse_inventory_change -- INDEX(a) is redundant when you also have INDEX(a,b).
  • Even after un-normalizing date, the query will still be a full table scan. This is because no index is useful against date >= eff AND date <= exp. What percentage of rows will pass that range test? If it is only a small fraction, then consider removing the failing rows from the table.
  • Building indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql
  • DW: http://mysql.rjweb.org/doc.php/datawarehouse
  • Summary tables: http://mysql.rjweb.org/doc.php/summarytables
  • How much RAM do you have? tmp_table_size should be no more than about 1% of RAM.innodb_buffer_pool_size` should be about 70% of available RAM.
  • Thanks @RickJames. Good comments here. It wasn't until you brought up the dim_date indexes that I noticed some of the ridiculousness there that apparently I had left behind during testing. I have removed redundant indexes as you suggested for cleanup. You're right too regarding why dim_date is required so unfortunately, it is not going anywhere. These changes I just mentioned all help cleanup the structure, but none improve the speed unfortunately. It may simply be necessary to create a summary table as you mentioned for speed purposes unless you have any other suggestions. – Ryan Griffith Dec 29 '16 at 15:08
  • I have 8GB of RAM, but am considering bumping this to 12GB. I can resize my VM as we have excess capacity available. – Ryan Griffith Dec 29 '16 at 15:12
  • Was my first bullet point correct about the missing clause in the query? Be sure to increase buffer_pool_size whenever you add RAM. – Rick James Dec 29 '16 at 17:43
  • Unfortunately it was not correct. The date_id in the fact_warehouse_inventory_change table is the date that the change took place (which is the same date as row_effective_date, so while date_id technically represents the same row in dim_date that would be referenced by row_effective_date, I need an entire span of dates between row_effective_date and row_expiration_date. Hope that makes sense. – Ryan Griffith Dec 29 '16 at 19:30
  • If all three columns (in fact...) were actual DATEs, the query would not need the other table for this query. And that would make a significance performance improvement. If we are still confusing each other, suggest you supply a short list of rows from both tables that exemplify the issues. – Rick James Dec 30 '16 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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