MySql query and table optimisation

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=InnoD开发者_如何学编程B 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'

If you are running that exact query over and over, then you could cache the results. Every time you insert into the table with impressionObjectId1 = 'C69A54B8-B828-E2E4-2319A93011DF4120' AND impressionObjectId2 = '1' then increment a counter, and every time you delete, decrement the counter.

If you have a relatively small number of uniqe cases for your query then this would be a good performance booster, although it is not as good for data integrity and must be used with care.

I notice in your explain that your 'ROWS' value is very high. About how many rows should be in your result set? (Note that ROWS in an explain is the number of rows that had to be searched to find the result set, not the number of rows IN the result set)

It's possible you can reverse the order of your index and gain some efficiency.

In general you want to put the most selective column first in the index, so that the number of possible matching rows is the smallest.

Here's a nice trick for finding the most selective column:

SELECT SUM(impressionObjectId1 = 'C69A54B8-B828-E2E4-2319A93011DF4120'),
       SUM(impressionObjectId2 = '1')
            FROM impression;

The most selective column will have the lowest SUM value. Put that column first in the index.

You could also just create 2 index, one a revers of the other and let MySQL pick the best.

When creating indices for VARCHAR fields it is often helpful to create the index only on the first X characters, using col_name(X) syntax.

The index is more efficient this way, assuming that the first X characters of the field are enough to differentiate between rows (depends on the type of data you have there). If your columns contain a GUID (as in col 1 of your example) or a very short text (as in col 2), creating the indices for only the first 10 chars or so can really improve performance.