Select COUNT (*) plus secondary index fast

Create Table yw (

ID int unsigned NOT NULL auto_increment,

c1 int NOT NULL default ' 0 ',

C2 int NOT null default ' 0 ',

Primary KEY (ID)

);

Creating a stored procedure call insert_yw inserting data into a table

Two parameters to improve insert performance when inserting

Innodb_flush_log_at_trx_commit = 0

Sync_binlog = 0

Call Insert_yw (4500000);

Select COUNT (*) from YW;

+----------+

| COUNT (*) |

+----------+

| 4500000 |

+----------+

1 row in Set (2.06 sec)

It's 2.06 seconds.

After the secondary index is added

Create index id_c1 on yw (C1);

>select Count (*) from YW;

+----------+

| COUNT (*) |

+----------+

| 4500000 |

+----------+

1 row in Set (0.68 sec)

Why use secondary index scan instead of primary key scan to come faster? We need to understand the difference between InnoDB's clustered index and secondary index.

InnoDB clustered index is the primary key and row data are saved together, and secondary index is stored separately, then a pointer to the primary key. Therefore, the total number of count (*) statistics is required to scan with secondary index, which is obviously faster.

This article is from the DBA Sky blog, so be sure to keep this source http://9425473.blog.51cto.com/9415473/1662704

Select COUNT (*) plus secondary index fast