Check if table exists without using "select from"

If you want to be correct, use INFORMATION_SCHEMA.

SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdb' 
    AND table_name = 'testtable'
LIMIT 1;

Alternatively, you can use SHOW TABLES

SHOW TABLES LIKE 'yourtable';

If there is a row in the resultset, table exists.


SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')

if you get a non-zero count, the table exists.


A performance comparison:

  • MySQL 5.0.77, on a db that has about 11,000 tables.
  • Selecting a non-recently-used table so it's not cached.
  • Averaged over 10 tries each. (Note: done with different tables to avoid caching).

322ms: show tables like 'table201608';

691ms: select 1 from table201608 limit 1;

319ms: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'mydb') AND (TABLE_NAME = 'table201608');

Note if you're running this a lot -- like over many HTML requests in a short period -- the 2nd will be way faster since it'll be cached an average 200 ms or faster.

Tags:

Mysql

Sql

Recent Posts