Index causing much slower query

[H M]

Hello,

I am running a query on a 13 million row partitioned table and have found that removing an index from the table or removing the partitioning improves the performance dramatically. I'm confused why this is happening and would appreciate any help.

My table is split into 12 partitions by 'intcol'. My query resembles the following:

SELECT * FROM table WHERE intcol=1 AND datetimecol IS NOT NULL;

There are 15 rows in the table with intcol=1, 5 of which are not null. I have an index for intcol,datetimecol. I have confirmed, using EXPLAIN, that MySQL is using my intcol,datetimecol index to resolve the query. EXPLAIN also reports that the query only touches partition #1. Partition #1 is only about 85Mb with a 50Mb index.

When I run the query on the table it takes some 6+ seconds to return the 5 rows. If I change the query to be simply "SELECT * FROM table WHERE intcol=1", the 15 result rows come immediately.

If I then remove the intcol,datetimecol index and replace it with just an intcol index, the original query returns immediately. It appears that placing the datetimecol column in the index is causing my query to take much longer than it should.

And even more confusing for me - if I rebuild the table without partitioning, the query is very efficient, returning immediately!

I am clearly misunderstanding how partitioning is affecting how my index is used. I want the intcol,datetimecol index because I anticipate this table having large numbers of rows matching intcol in the future, and expect partition pruning and the index to narrow my results based on first intcol and then datetimecol. For this reason, it is not satisfactory to simply have an intcol index.

Where am I going wrong?

We are using MySQL 5.1.26.

Thanks!