3

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ?

select count(id) from table1 where status='A' and empID=123

Table Indexes

PRIMARY KEY (`id`),        

KEY `empID` (`empID`),               
KEY `status` (`status`),    
KEY `testID` (`testID`),    

CONSTRAINT `fk_1` FOREIGN KEY (`empID`) REFERENCES `emp_info` (`id`)
Community
  • 1
IS_EV
  • 181
  • 3

1 Answers1

1

A composite index on (status, empID) will be faster. It might be better as (empID, status) depending on the cardinality of these two columns. An EXPLAIN would have shown you that it was only using one value against one index.

Alain Collins
  • 515
  • 2
  • 7