My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying
information_schema directly is even slower.
The database contains about 250 databases, with 100 to 200 tables per database (about 20k tables in total).
- How can I find out why these operations are so slow?
- Is there perhaps some setting I can change to make it run faster, or to cache it SQL-side?
(The application does about 14 such queries per page load - I'm well aware that this legacy code needs to be cleaned up, but looking for possible options while I work on the long-term fix.)