Re: MySQL partition by range on YEAR(date) weird results

[Adelin Feraru]

Hi!

I did not know that the "intersect(advertisment_id,domain_id)" would cause the nonsense results. For now I have fixed all my queries by doing the following kind of query:

EXPLAIN PARTITIONS SELECT a.advert_stats_id FROM ( SELECT * FROM adverts_stats_clicks WHERE click_on = 'www' AND (click_from='sel' OR click_from='top' OR click_from='s' OR click_from='e' OR click_from='adv') AND ( advertisment_id = '6122') AND added_on>='2012-10-01' AND added_on<='2014-12-31' ) a WHERE a.domain_id=618


Like this I got good rezults to the expense of a derived query.

id select_type table partitions type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 10675 Using where
2 DERIVED adverts_stats_clicks ac7,ac8,ac9 ref click_on,advertisment_id,added_on advertisment_id 4 9647 Using where

The domain_id and advertisment_id they were both KEYs before deciding to partiton the table. I have never thought that they would case this issue.

Can you please try to explain why having 2 separate keys on domain_id and advertisment_id would cause the intersect and the "weird issue" ?

Ioan