drizzle> show create table f; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | f | CREATE TABLE `f` ( `a` int NOT NULL, `b` int NOT NULL, `c` int NOT NULL, PRIMARY KEY (`a`,`b`,`c`), KEY `c` (`c`), KEY `b` (`b`,`c`) ) | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
drizzle> select TABLE_NAME, COLUMN_NAME, IS_INDEXED, IS_USED_IN_PRIMARY, IS_UNIQUE, IS_MULTI, IS_FIRST_IN_MULTI, INDEXES_FOUND_IN from data_dictionary.columns WHERE TABLE_NAME="f"; +------------+-------------+------------+--------------------+-----------+----------+-------------------+------------------+ | TABLE_NAME | COLUMN_NAME | IS_INDEXED | IS_USED_IN_PRIMARY | IS_UNIQUE | IS_MULTI | IS_FIRST_IN_MULTI | INDEXES_FOUND_IN | +------------+-------------+------------+--------------------+-----------+----------+-------------------+------------------+ | f | a | TRUE | TRUE | TRUE | TRUE | TRUE | 1 | | f | b | TRUE | TRUE | TRUE | TRUE | TRUE | 2 | | f | c | TRUE | TRUE | TRUE | TRUE | FALSE | 3 | +------------+-------------+------------+--------------------+-----------+----------+-------------------+------------------+ 3 rows in set (0 sec)
What is so cool about the above? Often people over index columns. They will over index the first part of keys not realizing that the first part of key can be used without creating a standalone key.
These are a few of the design goals in the above:
1) Give someone an easy view into whether a column is a part of a primary key.
2) Find out how many times you are indexes one column.
3) From a glance see what keys are first inline for a multipart key.
4) Allow a DBA to work with the database to find out problems (don't force them into tools until they need to see the state of clusters).
Anyone who has ever managed a MySQL database can pretty quickly see how valuable the above is. Doing a JOIN against a log table will quickly show you what queries are running without the advantage of indexes.
What is especially cool about the above?
1) None of that data is materialized. We generate the data through table functions which federate data from multiple engines (all using the new Storage Engine interface).
2) We open zero tables for the above queries. You can query the data dictionary without blowing through the table cache, or anyway affecting your running queries. In the past the information schema would force tables into the table cache in order to get data from them. In our world? We don't require that at all. If you want schema data we can provide it without undermining your active query sessions.
3) All of the above information is stored in our Google Protobuffer table format. We do no translations, so what you see is always what you get.
Pretty cool :)