2 mn without partitions , 29 mn with 26 partitions
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
;