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`)

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?

  • 2
    Do the tables are of equal size (both 10 and 100M rows in each case)? How many rows are returned? Do you have composite indexes on (field2, field1) or simple indexes on both columns? Are the tables myisam or innodb? It would help if you added the output of SHOW CREATE TABLE for both tables and also the EXPLAIN plan. – ypercubeᵀᴹ Feb 28 '15 at 20:09

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.

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.