Hi,
This is probably a cousine of bug #18198. When this bug is pushed to the
tree the number of supported partitioning functions will decrease,
particularly due to problems with character sets, collations and character
fields in general.
At the same time there is some new development on a new partitioning method
that will also handle fields with character sets and collations in a proper
manner.
PARTITION BY KEY will already now handle any type of fields correctly.
Rgrds Mikael
Jean-Pierre Arneodo Wrote:
-------------------------------------------------------
> 2 tables, same data. 2 questions.
> big.MYD and bigfoot#P#pA.MYD have the same data,
> 67 000 000 records.
> All data in bigfoot are in partition pA.
>
> Query: select nom,count(*) from xxx where nom like
> 'A%' group by nom;
> 152 rows in set (2 min 45.65 sec) for table big.
> 152 rows in set (29 min 29.45 sec) for table
> bigfoot.
>
> All partitions are empty except pA, what is the
> problem with partitions?
> bigfoot.MYI size is more than twice big.MYI, why?
>
> Details here:
>
> mysql> explain partitions select nom,count(*) from
> bigfoot where nom like 'A%' group by nom;
> +----+-------------+---------+------------+-------
> +---------------+------+---------+------+---------
> -+--------------------------+
> | id | select_type | table | partitions | type
> | possible_keys | key | key_len | ref | rows
> | Extra |
> +----+-------------+---------+------------+-------
> +---------------+------+---------+------+---------
> -+--------------------------+
> | 1 | SIMPLE | bigfoot | pA | index
> | nom | nom | 20 | NULL | 67108864
> | Using where; Using index |
> +----+-------------+---------+------------+-------
> +---------------+------+---------+------+---------
> -+--------------------------+
> 1 row in set (0.00 sec)
>
> mysql> show create table bigfoot;
>
> CREATE TABLE `bigfoot` (
> `id` int(11) NOT NULL default '0',
> `nom` char(20) NOT NULL default '',
> `prenom` char(20) NOT NULL default '',
> `ne_le` date NOT NULL,
> KEY `nom` (`nom`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> PARTITION BY RANGE ( ascii(upper(substring(nom
> from 1 for 1))) - 65)
> ( PARTITION p0 VALUES LESS THAN (0) ENGINE =
> MyISAM
> , PARTITION pA VALUES LESS THAN (1) ENGINE =
> MyISAM
> , PARTITION pB VALUES LESS THAN (2) ENGINE =
> MyISAM
> , PARTITION pC VALUES LESS THAN (3) ENGINE =
> MyISAM
> , PARTITION pD VALUES LESS THAN (4) ENGINE =
> MyISAM
> , PARTITION pE VALUES LESS THAN (5) ENGINE =
> MyISAM
> , PARTITION pF VALUES LESS THAN (6) ENGINE =
> MyISAM
> , PARTITION pG VALUES LESS THAN (7) ENGINE =
> MyISAM
> , PARTITION pH VALUES LESS THAN (8) ENGINE =
> MyISAM
> , PARTITION pI VALUES LESS THAN (9) ENGINE =
> MyISAM
> , PARTITION pJ VALUES LESS THAN (10) ENGINE =
> MyISAM
> , PARTITION pK VALUES LESS THAN (11) ENGINE =
> MyISAM
> , PARTITION pL VALUES LESS THAN (12) ENGINE =
> MyISAM
> , PARTITION pM VALUES LESS THAN (13) ENGINE =
> MyISAM
> , PARTITION pN VALUES LESS THAN (14) ENGINE =
> MyISAM
> , PARTITION pO VALUES LESS THAN (15) ENGINE =
> MyISAM
> , PARTITION pP VALUES LESS THAN (16) ENGINE =
> MyISAM
> , PARTITION pQ VALUES LESS THAN (17) ENGINE =
> MyISAM
> , PARTITION pR VALUES LESS THAN (18) ENGINE =
> MyISAM
> , PARTITION pS VALUES LESS THAN (19) ENGINE =
> MyISAM
> , PARTITION pT VALUES LESS THAN (20) ENGINE =
> MyISAM
> , PARTITION pU VALUES LESS THAN (21) ENGINE =
> MyISAM
> , PARTITION pV VALUES LESS THAN (22) ENGINE =
> MyISAM
> , PARTITION pW VALUES LESS THAN (23) ENGINE =
> MyISAM
> , PARTITION pX VALUES LESS THAN (24) ENGINE =
> MyISAM
> , PARTITION pY VALUES LESS THAN (25) ENGINE =
> MyISAM
> , PARTITION pZ VALUES LESS THAN (26) ENGINE =
> MyISAM
> );
>
> mysql> explain partitions select nom,count(*) from
> big where nom like 'A%' group by nom;
> +----+-------------+-------+------------+-------+-
> --------------+------+---------+------+----------+
> --------------------------+
> | id | select_type | table | partitions | type |
> possible_keys | key | key_len | ref | rows |
> Extra |
> +----+-------------+-------+------------+-------+-
> --------------+------+---------+------+----------+
> --------------------------+
> | 1 | SIMPLE | big | NULL | index |
> nom | nom | 21 | NULL | 67108864 |
> Using where; Using index |
> +----+-------------+-------+------------+-------+-
> --------------+------+---------+------+----------+
> --------------------------+
> 1 row in set (0.23 sec)
>
> mysql> show create table big;
>
> CREATE TABLE `big` (
> `id` int(11) default NULL,
> `nom` char(20) default NULL,
> `prenom` char(20) default NULL,
> `ne_le` date default NULL,
> KEY `nom` (`nom`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> ;
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com