MySQL Forums :: Partitioning :: wrong output generate while using partitions


Advanced Search

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
wrong output generate while using partitions 1883 Shivam Patel 12/13/2014 07:56AM
Re: wrong output generate while using partitions 904 Rick James 12/14/2014 10:33PM
Re: wrong output generate while using partitions 870 Shivam Patel 12/16/2014 01:24AM
Re: wrong output generate while using partitions 931 Jonathan Stephens 12/16/2014 02:32AM
Re: wrong output generate while using partitions 946 Shivam Patel 12/16/2014 04:00AM
Re: wrong output generate while using partitions 863 Rick James 12/16/2014 04:28PM
Re: wrong output generate while using partitions 974 Shivam Patel 12/17/2014 07:20AM
Re: wrong output generate while using partitions 985 Rick James 12/17/2014 11:27AM


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.