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

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.