Saturday, February 26, 2011

SHOW or INFORMATION_SCHEMA tables? Can MySQL developers PLEASE make up their minds!

Which one do you prefer?
SHOW TABLES
or
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE()
I know what I prefer these days! I have, I admit it, gotten used to the SHOW commands, and I find them useful in many cases. For example in the case above, if I don't have that many tables in the database and I only want to see what they are. a simple SHOW TABLES command does the trick nicely. And this is said by someone (myself) who really disliked the SHOW commands initially. And to be honest, I still dislike them in many cases, cases where the ability to filter, join, aggregate annd get an instance view (sic!) on things where only a proper SQL TABLE, such as the INFORMATION_SCHEMA.TABLES table, would do the trick. So all in all, they both have uses, the way I look at it is that SHOW commands are useful shortcuts for the INFORMATION_SCHEMA tables.

Regrettable, that is not how it works in real life. Rather, the INFORMATION_SCHEMA tables are largely based on what the SHOW commands return, which is a shame. Most it is a shame as many useful things with INFORMATION_SCHEMA tables, such as joins and aggregates, turns to being painfully slow even with a modest size instance.

And not only that. It was once the intention, IIRC, to make all SHOW commands available as INFORMATION_SCHEMA tables. But in recent MySQL versions, this is going in another, confusing, direction. MySQL 5.5 adds the SHOW RELAYLOG EVENTS command, for example. This returns data that would be really useful to do at least some filtering on, and possibly a join or two, but no, this is ONLY available as a SHOW command. You cannot open a cursor on a SHOW command either, which means that you cannot, in plain SQL, take data from it and store it in some other table, for example, so you cannot use MySQL, otherwise really useful, EVENTS here.

On the other hand, MySQL 5.5 introduces the PARAMETERS INFORMATION_SCHEMA table. This has no corresponding SHOW command? So if uou are to interactively call a procedure and want to look at the parameters, then you are back at the INFORMATION_SCHEMA tables again. Com one now, MySQL developers, make up your minds where you want stuff to be. And if you ask me, what I would want. No, correct that, require, at least in the long run:
  • All useful shortcuts to metadata available as SHOW commands, including some kind of filtering (the SHOW RELAYLOG EVENTS command lacks even the simple filtering along the lines of the SHOW TABLES and SHOW STATUScommands).
  • All available metadata available in INFORMATION_SCHEMA tables. Yes, that is ALL metadata.
  • And for the INFORMATION_SCHEMA tables, I don't really want a performance improvement, as currently there is no performance at all to improve. Can we please get SOME kind of performance on these tables, please.
In addition, a metadata repository would be nice. And if you ask me, I'd prefer to have that in a central repositiry, such as INFORMATION_SCHEMA. The whole idea of .frm files is hopelessly outdated I'm afraid. And yes, I understand the implications in terms of portability across versions and stuff, but you are smart guys, so I am sure you can fix that.

Cheerio
/Karlsson

1 comment:

Sheeri K. Cabral said...

By the way, one nice thing in MySQL 5.5 is that mysqldump will export INFORMATION_SCHEMA if you explicitly ask it to (not with --all-dabases, actually specifying it as a bareword), so that opens things up to a whole new level of being able to export and use tools like awk and sed to manipulate it.

That's hacky, I know, but at least we have more ways to be hacky. I also would like more consistency among SHOW and INFORMATION_SCHEMA tables.