I have a few large databases each has over 1,000 tables. If I use the "SHOW tables;" statement, then mysql returns all the names of the tables in any one of my large databases in about 0.07 seconds. Very fast. But if I use the standard statement: "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbname';", then it takes mysql more than 3 minutes to return all the table names from the databases. Does anyone know why the information_schema is so much slower using SELECT compared to SHOW? Is it not index? This is very strange.
