0

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

Improve this question
2
  • 1
    Any reason "SHOW TABLES;" won't work? Can you explain why you'd want to do something like this (how does your application not know what tables exist)? Commented Feb 13, 2012 at 22:49
  • SHOW TABLES probably will work, but I was looking for discussion on what would be the most efficient in terms of speed and memory. Also, do all users have privileges to run the 'SHOW TABLES' command? Commented Feb 14, 2012 at 9:41

4 Answers 4

Reset to default
1

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”.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

1

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>"`
Improve this answer

Comments

0 
SHOW TABLES LIKE "tablename";

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

Improve this answer

Comments