,Because the leaf node of the primary key index tree is data and the leaf node of the secondary index tree is the primary key value, the secondary index tree is much smaller than the primary key index tree. Step 2: Second, create a SQL statement that includes count query for all tables.
The following are the steps that help us to count the number of rows of all tables in a particular database: Step 1: First, we need to get all table names available in a database. | 1 | SIMPLE | pan_file | NULL | index | NULL | unq_id | 4 | NULL | 4806466 | 100.00 | Using index | MySQL also allows us to get the number of rows of all tables in a specific database. | 1 | SIMPLE | pan_file | NULL | index | NULL | idx_size | 8 | NULL | 4806466 | 100.00 | Using index |
Later for filenameand sizeAdded a normal index, SELECT COUNT(*) FROM file The query only takes less than 1 second, using idx_sizeIndexes.Īgain for idThe query time is still 1s, but the index is still unique. At first, there was no index, so it was not executed SELECT COUNT(*) FROM file The speed is extremely slow, about 20 seconds. The file table has more than five million rows of data.