Profiling a SELECT statement with an index

Question

I'm trying to show that when selecting from a table with an appropriate index the number of rows has almost no effect on the query latency. So I created a table:

CREATE TABLE `perf_test` (
  `first` int NOT NULL,
  `second` int NOT NULL,
  `third` int NOT NULL,
  `fourth` int NOT NULL,
  `fifth` int NOT NULL,
  KEY `yyy` (`first`)
) 

I ran two tests: one where the table has 1M rows, 100 of which match the query; the other where the table has 10M rows, 100 of which match the query. I issued the query SELECT * FROM perf_test WHERE first = 1 10 times.

The average run time for 1M rows was 0.007 seconds, for 10M rows it was 0.057, so an order of magnitude greater. Why would that be?

MySQL version is 8.0.19 and I'm running this on Windows 10, using MySQL command line client.

Edit (adding info from SHOW SESSION STATUS LIKE 'Handler%'; as per @RickJames suggestion):

mysql> select count(*) from perf_test where first = 1;        
+----------+                                                  
| count(*) |                                                  
+----------+                                                  
|      100 |                                                  
+----------+                                                  
1 row in set (0.00 sec)                                       
                                                              
mysql> SHOW SESSION STATUS LIKE 'Handler%';                   
+----------------------------+-------+                        
| Variable_name              | Value |                        
+----------------------------+-------+                        
| Handler_commit             | 19    |                        
| Handler_delete             | 11    |                        
| Handler_discover           | 0     |                        
| Handler_external_lock      | 34    |                        
| Handler_mrr_init           | 0     |                        
| Handler_prepare            | 4     |                        
| Handler_read_first         | 0     |                        
| Handler_read_key           | 10    |                        
| Handler_read_last          | 0     |                        
| Handler_read_next          | 1044  |                        
| Handler_read_prev          | 0     |                        
| Handler_read_rnd           | 0     |                        
| Handler_read_rnd_next      | 0     |                        
| Handler_rollback           | 0     |                        
| Handler_savepoint          | 0     |                        
| Handler_savepoint_rollback | 0     |                        
| Handler_update             | 0     |                        
| Handler_write              | 0     |                        
+----------------------------+-------+                        
18 rows in set (0.00 sec)                                     
                                                              
mysql> select count(*) from perf_test_10m where first = 1;    
+----------+                                                  
| count(*) |                                                  
+----------+                                                  
|      100 |                                                  
+----------+                                                  
1 row in set (0.00 sec)                                       
                                                              
mysql> SHOW SESSION STATUS LIKE 'Handler%';                   
+----------------------------+-------+                        
| Variable_name              | Value |                        
+----------------------------+-------+                        
| Handler_commit             | 20    |                        
| Handler_delete             | 11    |                        
| Handler_discover           | 0     |                        
| Handler_external_lock      | 36    |                        
| Handler_mrr_init           | 0     |                        
| Handler_prepare            | 4     |                        
| Handler_read_first         | 0     |                        
| Handler_read_key           | 11    |                        
| Handler_read_last          | 0     |                        
| Handler_read_next          | 1144  |                        
| Handler_read_prev          | 0     |                        
| Handler_read_rnd           | 0     |                        
| Handler_read_rnd_next      | 0     |                        
| Handler_rollback           | 0     |                        
| Handler_savepoint          | 0     |                        
| Handler_savepoint_rollback | 0     |                        
| Handler_update             | 0     |                        
| Handler_write              | 0     |                        
+----------------------------+-------+                        
18 rows in set (0.00 sec)                                     

Answer 1

The problem

A table that is bigger than the buffer_pool is likely to be slow. How much slower depends on the query, other activity, the selectivity of the WHERE, etc, etc. There are applications that have tables that are 100 times the buffer_pool, yet still run nicely.

innodb_buffer_pool_size is only 8M. This is extremely tiny. Change it to 70% of available RAM. (8M is a very old default; did you copy my.cnf from somewhere?)

Your 10MB table is too big to be cached in the buffer pool. So there was a lot of I/O going on -- hence it was slower.

After increasing the buffer_pool, your test should show no 'significant' difference in timing between a small table and a bigger table.

Further discussion

I would expect you to get the 'same' time for 10M rows as 1M rows. Since you did not, let me list some potential flaws in the test.

When using ENGINE=InnoDB, it is wise to explicitly include a PRIMARY KEY. (It won't impact the test you are doing, but it avoids quibbles about its absence.)

In typical timing tests, one finds that the first run takes a certain amount of time, then all other tests take a similar time, but different than the first. If you find the 'first' to be faster (or slower) then the rest, then I will elaborate further.

If the "Query cache" is turned on, the timings are bogus. One way to be sure it is not messing up the conclusion: add SQL_NO_CACHE. (The QC is off for MySQL 8.0, so this should not be an issue.)

Another approach is to show the number of "Handler" actions. I find this to be useful in comparing two potential queries, even if they involve so few rows that the timings are suspect. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

I would expect the Handler counts to be identical for your two tables, thereby indicating that they are identical in effort.

If the table (or the relevant index) is bigger than innodb_buffer_pool_size, there may be more I/O. I doubt if this is the case for your "small" tables, but it is worth checking. (Note: The Handler counting does not discover this difference.)

Do EXPLAIN SELECT ... for each. I don't expect any substantive difference.

Do EXPLAIN FORMAT=JSON SELECT ... for each. I don't expect any substantive difference.