MySQL Forums
Forum List  »  Partitioning

Index causing much slower query
Posted by: H M
Date: September 04, 2008 12:07PM


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.


Options: ReplyQuote

Written By
Index causing much slower query
September 04, 2008 12:07PM
September 04, 2008 12:14PM
September 04, 2008 12:36PM
October 03, 2008 10:07AM
October 08, 2008 07:46AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.