I have a slow query I’d like to optimize.
When I run the query in question, I can see this output in the slow query log:
# Time: 150504 15:42:27 # User@Host: root[root] @ localhost  Id: 37 # Query_time: 6.167118 Lock_time: 0.000311 Rows_sent: 490 Rows_examined: 2792 Rows_affected: 0 # Bytes_sent: 9664 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 126992 # InnoDB_trx_id: 2F6CC547 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 1623 InnoDB_IO_r_bytes: 26591232 InnoDB_IO_r_wait: 6.007482 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 2016
What I can see is the value for InnoDB_IO_r_wait is too high: above 6 seconds.
What does this mean? How can I optimize it?
The query in question is a join of two tables, one with about 6 million records the other with about 65 million rows.
Server version: 5.6.22-72.0-log Percona Server (GPL), Release 72.0, Revision 738
Machine: virtual box with 10 cores and 16 GB RAM.