I have a table with more than 15m rows. I need the total number of rows. So:
SELECT COUNT(*) FROM thetable;
Which takes around 50s to complete. Explaining gives me Select tables optimized away
. I suppose this means that the result can be found only by using an index, then why does it still take so long? Here are some information about the index on the id
column (It's non nullable):
Index Type: BTREE (clustered)
Cardinality: 14623100
Unique: YES
How can I improve the performance of this query? Thanks.
Note: The database is MySQL 5.7.1 and using InnoDB engine.
EDIT:
Create statement:
CREATE TABLE `properties` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(255) DEFAULT NULL,
`locality` varchar(50) DEFAULT NULL,
`latitude` decimal(13,9) DEFAULT NULL,
`longitude` decimal(13,9) DEFAULT NULL,
`state` varchar(10) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
.....
PRIMARY KEY (`id`),
KEY `index_properties_on_address` (`address`),
KEY `index_properties_on_latitude` (`latitude`),
KEY `index_properties_on_longitude` (`longitude`),
KEY `index_properties_on_state` (`state`),
KEY `index_properties_on_created_at` (`created_at`),
.....
) ENGINE=InnoDB AUTO_INCREMENT=28267712 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
Note: I omitted some lines, there are 44 columns.
Explain plan:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+