Guruhowtos

One command, which few people realize exists is SHOW OPEN TABLES - it allows you to examine what tables do you have open right now:

This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table is opened more than ones)

In_use show how many threads are currently using this table, meaning have it locked or waiting on the table lock for this table to lock it.

Name_locked shows whenever the name for this table is locked. It is used for DROP or RENAME TABLE, so you would very rarely see this field to contain anything else than 0.

Besides just figuring out what tables are in the table_cache this command is rather helpful to understand if there is activity on the given table. Just run “FLUSH TABLES mytable” and examine open tables later – if you see this table in table cache again chances are it is being used.

Note however if you’re starting MySQL Command line client without “-A” option it opens all tables in the active database to allow tab completion which can screw results.