MySQL Forums
Forum List  »  Partitioning

Re: MySQL partition by range on YEAR(date) weird results
Posted by: Adelin Feraru
Date: May 15, 2014 03:38AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL partition by range on YEAR(date) weird results
1198
May 15, 2014 03:38AM


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.