1

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!

  • 1
    Post SHOW CREATE TABLE sales instead of DESCRIBE. – Akina Apr 19 at 9:26
  • 2
    To optimize this queries you need index by (sale_date, item_link) whereas your primary has backward fields order. And for the first query single (sale_date) index is enough. – Akina Apr 19 at 9:29
  • 2
    Why did you choose VARCHAR for the price and date? VARCHAR is very expensive for search, that is why specialized data types exists. – Kondybas Apr 19 at 10:00
  • 1
    check the comment from @Akina , it is an answer – a_vlad Apr 19 at 10:54
  • @Akina I've done all you've suggested and now it is blazing fast, thanks! – Acidon Apr 19 at 12:48
2

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,

  • Thanks, I didn't know this, great information and helped my case! – Acidon Apr 19 at 12:50

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.