Mysql – query slows down exponentially as table grows

MySQL

I have the following query running on mysql 5.6 on my CENTOS server

select tb1.field2, tb1.field1, tb2.field1 
from dns.table1 tb1, dns.table2 tb2 
where tb1.field2 = tb2.field2 
  and tb1.field1 != tb2.field1

All relevant columns are indexed. On tables of 10 million rows, it finishes within 400 seconds, however, on 100 million rows, it finishes within 10 hours!

table schema:

CREATE TABLE `my_table` (
  `table_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `field2` varchar(255) NOT NULL,
  `field1` varchar(255) DEFAULT NULL,
  `field3` longtext,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`table_id`),
  UNIQUE KEY `field2` (`field2`),
  KEY `field1` (`field1`)
) ENGINE=InnoDB 
  AUTO_INCREMENT=1 
  DEFAULT CHARSET=utf8 
  ROW_FORMAT=COMPRESSED 
  KEY_BLOCK_SIZE=8 |

Both innodb tables are of equal sizes.

Why? I suspect file swapping within mysql, what can be optimized on mysql? Would increasing the buffer pool help?

Best Answer

The compound INDEX(field2, field1) would be a "covering" index. This would avoid a huge number of lookups, thereby making it much faster. Note: This will not work if you also SELECT field3. (InnoDB and MyISAM would both benefit in this case.)

Keep your UNIQUE(field2) because of the uniqueness constraint.

Related Question