Why is table opened every time a query is executed against it?

Question

I execute this SQL command twice and profile

select * from sbtest.sbtest2 limit 100;

The first time profile result is

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000124 |
| checking permissions | 0.000015 |
*| Opening tables       | 0.163660 |*
| System lock          | 0.000029 |
| init                 | 0.000056 |
| optimizing           | 0.000017 |
| statistics           | 0.000073 |
| preparing            | 0.000228 |
| executing            | 0.000007 |
| Sending data         | 0.001327 |
| end                  | 0.000019 |
| query end            | 0.000008 |
| closing tables       | 0.000013 |
| freeing items        | 0.004998 |
| logging slow query   | 0.000015 |
| cleaning up          | 0.000009 |
+----------------------+----------+
16 rows in set (0.00 sec)

This is the first time6 ,so it needs to "open tables" from hard disk, and above is the second time profile.

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000012 |
*| Opening tables       | 0.000026 |*
| System lock          | 0.000013 |
| init                 | 0.000025 |
| optimizing           | 0.000006 |
| statistics           | 0.000016 |
| preparing            | 0.000013 |
| executing            | 0.000003 |
| Sending data         | 0.000296 |
| end                  | 0.000008 |
| query end            | 0.000006 |
| closing tables       | 0.000010 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

The second time "open tables time" is 0.00026, which is much faster than the first time "open tables time" 0.163660.

What I don't understand is when I use innodb buffer pool and my open_table_cache is using, why I still need to open tables from hard disk ?

Answer 1

Perhaps the caption of that step isn't entirely accurate and what it is doing is "obtaining handle to access tables"? In that case the first call performs the relevant IO to open the table (read its metadata and prepare anything that is needed in memory) before returning the handle(s) so the next step can start accessing the resource, and the second time the routine will see that it already has all that done.

It is common (and generally good design, keeping stages decoupled from each other's detailed logic) for routines not to know when the sub-routine they call actually does some real work or just returns a cached resource. Good development avoid early optimisation (make it work, then make it work fast) so initially the code would go to disk every time but later versions include the caching logic.

Another alternative: maybe it really is redoing everything every time, but the database's page cache and/or the OS disk cache mean it is faster because there is less physical IO?

Answer 2

Every time a table is accessed for parsing a query, all the tables mentioned in the query must be checked to see if it exists. This requires looking for the .frm of the table.

Here is how you can tell

If you run

USE mydb
SHOW TABLES;

the .frm files are scanned and tablenames are displayed.

If you run

USE mydb
SELECT table_name,engine FROM information_schema.tables WHERE table_schema=DATABASE();

the table files are accessed a little more aggressively.

If the table is MyISAM, the .MYD and .MYI files may need to be accessed to ensure usability.

If the table is InnoDB, then the data dictionary needs to be consulted.

Any table being opened the first time will require opening one or more file handles to the physical files. Opening the same table thereafter may reach out to the table cache, but it is still considered opening the table.

Even, a MEMORY table has its .frm opened : See my post I am using the MEMORY storage engine but MySQL still writes to my disk...Why?