MySQL Forums
Forum List  »  Partitioning

Re: wrong output generate while using partitions
Posted by: Shivam Patel
Date: December 17, 2014 07:20AM

I have tried your suggestions.

1. Created PK but still generated wrong output.
2. I can not change mysql version so I did not try it.
3. Turn off index_merge_intersection and now output is coming proper.

It is clear now that due to index_merge, output was coming wrong on partitioned table (but yet needed to investigate why is it so with partitioned table and not with non partitioned).

Also my investigation from other sites, I found that to avoid index_merge, we have many ways as per below from which I choose 3rd option now to solve my case.

1. use hint in query (which require change in existing queries in my case, so i avoid this option)

2. Turn off index_merge_intersection (which may impact other queries performance so i avoid this option)

3. create composite indexes (I used this option. I have created two composite indexes
ALTER TABLE TABLE1 ADD INDEX TABLE1_COMBO1 (COLUMN2,COLUMN1);

ALTER TABLE TABLE1 ADD INDEX TABLE1_COMBO2 (COLUMN3,COLUMN1);

4. create query with sub queries (which require change in existing queries in my case, so i avoid this option).

now my queries are working fine on partitioned table with above created two indexes.

can you explain why index_merge is coming on partitioned table and not coming on non partitioned table?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: wrong output generate while using partitions
1095
December 17, 2014 07:20AM


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.