MySQL Forums :: Partitioning :: Index on partitions


Advanced Search

Index on partitions
Posted by: Rathi Rao ()
Date: October 12, 2016 06:57AM

Hi,

I have a table with 2 partitions. Partitions are pactive = 1 and pinactive = 0. I understand that 2 partitions does not make so much of a gain, but I have used it to truncate and load in one partition and plain inserts in another partition.

The problem comes when I create indexes.

Query goes this way

select partitionflag,companyid,activityname from customformattributes where companyid=47
and activityname = 'Activity 1' and partitionflag=0

Created index - create index idx_try on customformattributes(partitionflag,companyid,activityname,completiondate,attributename,isclosed)

there are around 200000 records that will be retreived from the above query. But the query along with the mentioned index takes 30+ seconds. What is the reason for such a long time? Also, if remove the partitionflag from the mentioned index, the index is not even used.

And is the understanding that,

1. Even with the partitions available, the optimizer needs to have the required partition mentioned in the index definition, so that it only hits the required partition ---- Correct?

Any ideas on understanding this would be very helpful

Thanks in advance
Rathi

Options: ReplyQuote


Subject Views Written By Posted
Index on partitions 447 Rathi Rao 10/12/2016 06:57AM
Re: Index on partitions 203 Rick James 10/16/2016 03:33PM


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.