MySQL: What do we mean by 0 value in in_use column of show open tables statement? If I run show open tables command, system return 73 records. But, in_use column value for every record is 0. What does this mean?


Are these tables available in cache but not used by any thread?
From the online manual

http://dev.mysql.com/doc/refman/5.6/en/show-open-tables.html

The number of table locks or lock requests there are for the table. For example, 
if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1.
If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client
will block waiting for the lock, but the lock request causes In_use to be 2.


I have done this simple test:

mysql&gt; show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql&gt; lock table world.City write;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| world    | City  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql&gt; unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; show open tables like 'City';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked | 
+----------+-------+--------+-------------+
| world    | City  |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)


The number of rows of "show open tables" is about the one returned by "mysqladmin status". Mysql has a table cache that contains file descriptors for the table data file.

mysql@ubu-14:~$ mysqladmin status
Uptime: 1391  Threads: 4  Questions: 81  Slow queries: 0  Opens: 75  
Flush tables: 1    Open tables: 68  Queries per second avg: 0.058

mysql&gt; show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database           | Table                                              | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name         |      0   |           0 |
+--------------------+----------------------------------------------------+--------+-------------+
75 rows in set (0.00 sec)