Search code examples
mysqlsqldatabaseselectinformation-schema

why query on infomation_schema takes time?

I am running a very simple select query on some tables of information_schema but it always take too much of time.

For example:

select * from REFERENTIAL_CONSTRAINTS limit 3 ;

It takes around 34 seconds.

This query is very simple, no need table scan i think, no need of any condition etc. So why it takes too much of time.

some other tables in information_Schema also takes lot of time.

Thanks

Solution

  • The information_schema tables are not really tables. They are a mechanism that exposes server internals via the SQL interface. The responses to these queries are not from data that is "stored in a table" in any sense that you might expect -- it's "collected" each time the query is run.

    The level of communication between the SQL layer and the lower layers that collect the data does not always support the optimizations you might expect; for example, the LIMIT here is most likely not making it down -- the entire table is being rendered internally and then all but the first three rows are discarded... so this query is probably just as slow with and without the limit.

    Two general rules of thumb with information_schema -- which are really valid for all of SQL, but particularly here, are to select only the columns you need (not *, which will potentially require the server to do more work than necessary if you do not really need all the columns returned) and specify WHERE, both of which may reduce the amount of internal work being done.

    Another potential performance killer is heavy-handed tweaking ("tuning") of server variables. Most variables on most systems need to be left alone often than they are. Some of them, like table_open_cache can even cause the server to perform worse, the more "optimally" you tune them.