MySQL Forums
Forum List  »  Partitioning

Re: Count(*) query is slower on partitioned table
Posted by: Darren Demicoli
Date: December 13, 2016 02:50PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Count(*) query is slower on partitioned table
424
December 13, 2016 02:50PM


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.