I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete.
product_base (1.5GB, 26 million rows)
CREATE TABLE `product_base` ( `EAN13` varchar(13) NOT NULL DEFAULT '', `company_tk` int(11) DEFAULT NULL, `category` int(11) DEFAULT NULL, `modified` varchar(30) DEFAULT NULL, KEY `EAN13` (`EAN13`), KEY `company_tk` (`company_tk`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
attribute (11.8GB, 126 million rows)
CREATE TABLE `attribute` ( `EAN13` varchar(13) DEFAULT NULL, `attribute_type` int(11) DEFAULT NULL, `val_t` varchar(1500) DEFAULT NULL, `val_n` decimal(15,4) DEFAULT NULL, `val_d` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, KEY `EAN13` (`EAN13`), KEY `attribute_type` (`attribute_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
attribute_type (16KB, 59 rows)
CREATE TABLE `attribute_type` ( `attribute_type` int(11) DEFAULT NULL, `title` varchar(90) DEFAULT NULL, `field_name` varchar(90) DEFAULT NULL, `data_type` char(3) DEFAULT NULL, `seq` int(11) NOT NULL, `html` int(11) DEFAULT NULL, KEY `attribute_type` (`attribute_type`), KEY `seq` (`seq`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Example slow query:
Query_time: 61.629684 Lock_time: 0.000138 Rows_sent: 1 Rows_examined: 28141979 select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name from product_base p inner join attribute a on p.EAN13=a.EAN13 inner join attribute_type t on a.attribute_type=t.attribute_type where p.EAN13=705632109762 and (a.attribute_type=1 or a.attribute_type=3) order by t.seq;
Running explain
on that query:
+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+ | 1 | SIMPLE | t | range | attribute_type | attribute_type | 5 | NULL | 2 | Using where; Using filesort | | 1 | SIMPLE | a | ref | EAN13,attribute_type | attribute_type | 5 | eandata.t.attribute_type | 6299937 | Using where | | 1 | SIMPLE | p | ref | EAN13 | EAN13 | 54 | eandata.a.EAN13 | 1 | | +----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+
Server/OS:
Debian 8 64bit | 24GB RAM (MySQL is using 70% of this) | 60GB SSD | 2xIntel Xeon CPU E5506 @ 2.13GHz | MySQL 5.5.60
MySQL my.cnf
# * Fine Tuning key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP max_connections = 20 table_cache = 9552 thread_cache = 10 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 16M query_cache_size = 512M join_buffer_size = 2G innodb_buffer_pool_size = 15G innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64 #innodb_log_file_size = 512M innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_buffer_pool_instances = 8 max_heap_table_size = 2G tmp_table_size = 2G wait_timeout = 120 interactive_timeout = 120
Any suggestions for the slow queries?
Best Answer
I'm answering my own question here. The other answers have some great optimisation tips, but the key to getting my queries down from 60s to 100ms was to treat the EAN13 field as a string in the where statement.
As an example:
where p.EAN13=705632109762
becamewhere p.EAN13='705632109762'