MySQL Forums
Forum List  »  Partitioning

does indexing on partitioned tables scale?
Posted by: mark devaney
Date: May 18, 2009 01:56PM

It appears that there is some room for improvement in selects for partitioned tables where the individual partition indexes would fit into RAM but the combined set does not.

For example:

MyISAM table with 2 partitions of 1GB each (based on .MYI file size) and a 1GB sort buffer.

A query that prunes to a single partition runs very quickly (say 1 second) as the indexing occurs in RAM. However, a query that prunes to both partitions takes 10x as long (5 minutes).

I assume this is due to some logic that is trying to load all the pertinent indexes into RAM and if there's not enough room resorting to disk-based search.

Is it not possible to run RAM-based indexes N times and combine the intermediate results at the end so that the select time would scale better? I.e., I'd expect a two partition query to take roughly twice as long as a one partition query, not 10x.

Is there some system variable I'm not setting properly that would fix this problem? Bumping the sort buffers is not an option as the full database has 100's of partitions and each partition index is ~3GB.

Thanks in advance.

Edited 1 time(s). Last edit at 05/18/2009 02:52PM by mark devaney.

Options: ReplyQuote

Written By
does indexing on partitioned tables scale?
May 18, 2009 01:56PM

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.