Hi Rick
Thanks for your reply.
I am adding a varchar(1000) column and populating all fields with the value 'Sometext'. I have noted that if I specify a varchar with a shorter length in the definition, the degradation is less noticeable (example: varchar(30) ).
From the EXPLAIN, the same index is always being used: KEY `idx_b` (`class`).
Below is a followup transcript showing the mentioned degradation.
MariaDB [my_database]> show create table my_table_partitioned \G
*************************** 1. row ***************************
Table: my_table_partitioned
Create Table: CREATE TABLE `my_table_partitioned` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`class` enum('a','b','c','d','e','f') NOT NULL,
`comment` varchar(30) NOT NULL DEFAULT 'Sometext',
PRIMARY KEY (`id`),
KEY `idx_b` (`class`)
) ENGINE=InnoDB AUTO_INCREMENT=40000401 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION `start` VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (90000000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (160000000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (210000000) ENGINE = InnoDB,
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (7.01 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (6.87 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (6.89 sec)
MariaDB [my_database]> explain select sql_no_cache count(*) from my_table_partitioned;
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| 1 | SIMPLE | my_table_partitioned | index | NULL | idx_b | 1 | NULL | 19923579 | Using index |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
1 row in set (0.00 sec)
MariaDB [my_database]> alter table my_table_partitioned add comment2 varchar(1000) not null default "Sometext";
Query OK, 0 rows affected (6 min 1.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (10.17 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (9.71 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (9.74 sec)
MariaDB [my_database]>
MariaDB [my_database]> explain select sql_no_cache count(*) from my_table_partitioned;
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| 1 | SIMPLE | my_table_partitioned | index | NULL | idx_b | 1 | NULL | 19409353 | Using index |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
1 row in set (0.00 sec)
MariaDB [my_database]> alter table my_table_partitioned add comment3 varchar(1000) not null default "Sometext";
Query OK, 0 rows affected (6 min 51.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (11.45 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (11.05 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (10.66 sec)
MariaDB [my_database]> alter table my_table_partitioned add comment4 varchar(1000) not null default "Sometext";
Query OK, 0 rows affected (7 min 26.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (13.73 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (13.66 sec)
MariaDB [my_database]> select sql_no_cache count(*) from my_table_partitioned;
+----------+
| count(*) |
+----------+
| 20000200 |
+----------+
1 row in set (14.11 sec)
MariaDB [my_database]> explain select sql_no_cache count(*) from my_table_partitioned;
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
| 1 | SIMPLE | my_table_partitioned | index | NULL | idx_b | 1 | NULL | 19888078 | Using index |
+------+-------------+----------------------+-------+---------------+-------+---------+------+----------+-------------+
1 row in set (0.00 sec)
--
Darren Demicoli
Devops Engineer
http://www.xcaliber.com