Re: wrong output generate while using partitions

[Shivam Patel]

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?