Data Dictionary Fun in Drizzle

« previous entry | next entry »
Mar. 2nd, 2010 | 11:56 am

How often do people get indexing wrong? All the time. This is a sample table in Drizzle, I'll show how you can view indexes with the new data dictionary code.

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 :)

Link | Leave a comment | Add to Memories | Share

Comments {4}

Basil Sviridoff

(no subject)

from: vsviridov
date: Mar. 3rd, 2010 01:58 am (UTC)
Link

Oh noes, <pre> rips through friends page destroying layouts.
Please use lj-cut :)

Reply | Thread

Jobin Augustine

Cool stuff

from: jobinau
date: Mar. 5th, 2010 08:44 am (UTC)
Link

This is pretty coll stuff for DBAs.
Thanks Brian for making things simpler.

Reply | Thread

Jobin Augustine

will you share this for wiki.

from: jobinau
date: Mar. 5th, 2010 09:11 am (UTC)
Link

Will you allow me to copy content of your blog posts to documentation (Drizzle Wiki)

Reply | Thread

Brian "Krow" Aker

Re: will you share this for wiki.

from: krow
date: Mar. 5th, 2010 05:30 pm (UTC)
Link

Please feel free to!

Reply | Parent | Thread