CODEWITHSUNDEEP
mysql
user1067235
user1067235

Reputation:

What's the most efficient way in MySQL to test whether a table exists?

I'm trying to remove/reduce wasteful queries in my application to improve the speed. One query I have checks to see whether a table exists. It does this by trying to select the primary key field from the table and adds 'Limit 1' to the end of the query so that it doesn't select the whole table.

My question is this: is the most efficient way to determine whether a table exists or is there an even more efficient way?

For a little more info, I probably can't select from information schema as not all users will have access to that table

EDIT

The application allows for the installation of plugins, some of which integrate 3rd party systems. The query above is used to check whether the user has entered the database information for the 3rd party system correctly. First the system connects to the database and if that works, tries to query a table using the user specified table prefix

Upvotes: 0

Views: 204

Answers (4)

Web User
Web User

Reputation: 7736

MySQL 5.0 onwards, this is the way to do it:

SELECT COUNT(*)
       FROM information_schema.tables 
       WHERE table_schema = '[database name]'
             AND table_name = '[table name]';

I have also done it like this in the past:

`SHOW TABLES WHERE Tables_in_<database-name> = "<table-name>"`

Upvotes: 1

JYelton
JYelton

Reputation: 36512

SHOW TABLES LIKE "tablename";

More examples and tutorial here: http://www.electrictoolbox.com/check-if-mysql-table-exists/

Upvotes: 0

Bruno Silva
Bruno Silva

Reputation: 3097

Have you tried SHOW TABLES LIKE 'Tablename'? You might not have access to it as well.

Upvotes: 1

user830639
user830639

Reputation:

You can use the SHOW TABLES command. To test if its like a certain name, try:

SHOW TABLES LIKE 'nameOfTable';

According to the MySql Dev page, the full specification for SHOW TABLES is:

SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list using the mysqlshow db_name command. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 18.19, “Extensions to SHOW Statements”.

Upvotes: 1

Related Questions