MySQL Forums
Forum List  »  Partitioning

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

I have a table defined as below:

CREATE TABLE `adverts_stats_clicks` (
`advert_stats_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`advertisment_id` int(10) unsigned NOT NULL DEFAULT '0',
`domain_id` int(10) unsigned NOT NULL DEFAULT '0',
`catdom_id` int(10) unsigned NOT NULL DEFAULT '0',
`added_on` date NOT NULL DEFAULT '0000-00-00',
`ipfrom` varchar(64) NOT NULL DEFAULT '',
`click_on` varchar(10) NOT NULL DEFAULT '',
`click_from` varchar(5) NOT NULL DEFAULT '',
`click_from_path` char(3) NOT NULL DEFAULT '',
`country_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`advert_stats_id`,`added_on`),
KEY `domain_id` (`domain_id`),
KEY `click_on` (`click_on`),
KEY `advertisment_id` (`advertisment_id`),
KEY `country_id` (`country_id`),
KEY `added_on` (`added_on`)
) ENGINE=MyISAM AUTO_INCREMENT=48176808 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(added_on))
(PARTITION ac0 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION ac1 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION ac2 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION ac3 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION ac4 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION ac5 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION ac6 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION ac7 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION ac8 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION ac9 VALUES LESS THAN (2015) ENGINE = MyISAM,
PARTITION ac99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Then if I run the following query:

SELECT advert_stats_id FROM adverts_stats_clicks WHERE domain_id=618 AND 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'

I get for example 152 results. But if I change the date range as fllows:

SELECT advert_stats_id FROM adverts_stats_clicks WHERE domain_id=618 AND 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>='2013-10-01' AND added_on<='2014-12-31'

I get 643 results. Which is nonsense as the previous date range is bigger than the second. If I run explain partitions I get the following:

id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE adverts_stats_clicks ac7,ac8,ac9 index_merge domain_id,click_on,advertisment_id,added_on advertisment_id,domain_id 4,4 NULL 114 Using intersect(advertisment_id,domain_id); Using where

So in the first query the correct partitions (ac7,ac8,ac9) were selected. This proves that pruning is working. For the second query also the correct partitions were selected (ac8, ac9) however the results retrieved by both cannot be trusted.

What is happening ??? Why is like this?

Some extra info from sever variables in case it might me important to figure out.

Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database latin1
character_set_filesystem binary
character_set_results utf8mb4
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/

Please help and ask additional questions if you need more info in order to help.

Thank you!

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL partition by range on YEAR(date) weird results
2850
May 12, 2014 02:15AM


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.