MySQL count (*) query Problems

Select count (*) from tablename. The query speed has a great relationship with the table type (4 million pieces of data, InnoDB Table 2 minutes 19 seconds, MyISAM type table less than one second ). This is mainly because the MyISAM Table saves the number of rows in the table and can be directly returned, while the InnoDB table does not save the number of rows, which requires full table scanning.

So how can I speed up this query for an indodb table?

The test is as follows: ID is the primary key, PID is the foreign key, both of which have unique indexes, and name column has no index.

Add the WHERE clause:

Where ID> 0, which is equivalent to 2 minutes and 19 seconds without the WHERE clause.

Where pid> 0, very fast, 3.32 seconds.

Select count (ID) is basically the same as Count.

Select count (name) from tablename it takes more than two minutes to add a where clause to the query.

Summary: For an InnoDB table, use count (*) or count (Primary Key), and add the where Col condition. The Col column is a column other than the auto-increment primary key in the table that has unique constraints on the index. In this way, the query speed will be fast. To avoid full table scanning.