MySql query and table optimisation
- by Cheeky
Hi everyone.
I am trying to run the following simple query on a table with 500K records.
SELECT COUNT(*) AS impressionCount
FROM impression
WHERE 0 = 0
AND impressionObjectId1 = 'C69A54B8-B828-E2E4-2319A93011DF4120'
AND impressionObjectId2 = '1';
This query is taking 10 seconds to run. I have tried creating individual indexes for the impressionObjectId1 and impressionObjectId2 columns, as well as a composite index using both. The composite worked well for a while, but now it is also slow.
Here is my table structure:
DROP TABLE IF EXISTS `impression`;
CREATE TABLE `impression` (
`impressionId` varchar(50) NOT NULL,
`impressionObjectId1` varchar(50) NOT NULL,
`impressionObjectId2` varchar(50) default NULL,
`impressionStampDate` datetime NOT NULL,
PRIMARY KEY (`impressionId`),
KEY `IX_object` (`impressionObjectId1`,`impressionObjectId2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 191488 kB';
Any advice would be greatly appreciated. Thanks
EDIT: When adding an EXPLAIN, this is the output:
1, 'SIMPLE', 'impression', 'ref', 'IX_object', 'IX_object', '105', 'const,const', 304499, 'Using where; Using index'
