For the information schema.processlist:

enter image description here

Why are there no indexes on any of the fields (for example, a pk on ID)? Is this something that is not written through 'via sql' and is only reading directly from the storage layer to show the stats, or why isn't this table (and all other information_schema tables) constructed like a normal relational table?

And from the top of the mysql docs about them:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name

  • as explained here VIEWs have no indexes – nbk Nov 6 '20 at 21:49
  • Which version? There was a radical change in the implementation of information_schema in 8.0. Meanwhile, although VIEWs do not have indexes, the sometimes make use of indexes in the underlying table(s). – Rick James Nov 6 '20 at 23:11

Also the way the information schema tables is generated internally means that the entire table is generated before a single attempt at filtering is done on the table.

An index has no meaningful effect on information_schema tables.

So for performance considerations, don't overly use information_schema tables, there could be performance impacts on the entire system.

note: above refers to MySQL-5.7 and MariaDB information_schema. I haven't looked at the MySQL-8.0 implementatoin.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.