MySQL – Optimize InnoDB Query for SUM and ORDER BY

innodbMySQLoptimizationperformancequery-performance

I have an MySQL InnoDB table with 1.3M rows and 670MB size:

+----------------+--------------+------+-----+---------+-------+
|     Field      |     Type     | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| item_name      | varchar(255) | NO   |     | NULL    |       |
| item_link      | varchar(50)  | NO   | PRI | NULL    |       |
| item_price     | varchar(10)  | NO   |     | NULL    |       |
| shop_name      | varchar(40)  | NO   | MUL | NULL    |       |
| sales          | int(11)      | NO   |     | NULL    |       |
| sale_date      | varchar(10)  | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

CREATE TABLE `sales` 
  ( 
     `item_name`      VARCHAR(255) NOT NULL, 
     `item_link`      VARCHAR(50) NOT NULL, 
     `item_price`     VARCHAR(10) NOT NULL, 
     `shop_name`      VARCHAR(40) NOT NULL, 
     `sales`          INT(11) NOT NULL, 
     `sale_date` VARCHAR(10) NOT NULL, 
     PRIMARY KEY (`item_link`, `sale_date`), 
     KEY `sale_date` (`sale_date`), 
     KEY `shop_name` (`shop_name`) 
  ) 
engine=innodb 
DEFAULT charset=latin1

Here are my my.ini settings for my 3GB RAM server:

key_buffer = 16M
max_allowed_packet = 16M
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_autoinc_lock_mode=0
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M

innodb_buffer_pool_size = 1.5G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 350M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

When I run next query it takes over 10-15 seconds to return the results:

SELECT item_name, 
       item_link, 
       item_price, 
       shop_name, 
       sales 
FROM   `sales` 
WHERE  sale_date = '2019-04-18'

and even longer for:

SELECT item_name, 
       item_link, 
       item_price, 
       shop_name, 
       Sum(sales) AS sales 
FROM   `sales` 
WHERE  sale_date BETWEEN '2019-04-17' AND '2019-04-18' 
GROUP  BY item_link

I have a composite primary key for item_link and sale_date, I've also added indexes for shop_name and sale_date.

I don't know what else to do and I think this kind of query should perform way faster for such a relatively small table, please help!

Best Answer

You have indexes on item_link and sale_date but you are not using item_link. When The first column of index are not in use in the where clause, the index are tends to be skipped while query execution.

Plan you index as per you where clause which in this case sale_date would be a good

Thanks,

Related Question