VISUAL EXPLAIN part 2

VISUAL EXPLAIN will be come a standard tool in your DBA toolbox but you will need later versions of both MySQL Workbench and MySQL Server. The examples here were created with Workbench 5.2.44 and MySQL 5.6.8, the latest Release Candidate of MySQL 5.6. And both are available for free from http://Dev.MySQL.Com.

Last time I took this query

SELECT City.Name, Country.Name
FROM City
JOIN Country on (City.CountryCode=Country.Code)

and received this illustration of what the optimzer is planning to do.

Figure 1

But what is the picture telling us? The red block in the lower left maked Country(ALL) with the ’239′ above is letting us know that there will need to be full table scan of the Country Table from the ALL by the table name. The orange block on the lower right is saying the City table has a index name CountryCode with a key length of 3 bytes and a worst case of eight reads to get a record. The arrow pointing from the orange rectangle to the red rectangle lets us know that the Code column from the Country table is being deferenced (ref) for the join. And the blue box in the middle is confirming that the optimizer will be running a nested loop join to get the data. But what is missing that we had in the comamnd line version?

EXPLAIN SELECT City.Name, Country.Name FROM Country JOIN City on (City.CountryCode=Country.Code)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Country
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: world.Country.Code
rows: 8
Extra: NULL
2 rows in set (0.00 sec)

Hmmm, possible_keys? Can I trade off nice visuals for information on possible keys? Well, maybe.

Lets take a look at another example.
SELECT *
FROM City
WHERE ID > 1200;

VISUAL EXPLAIN provides us with this image.

Figure 2

Ths time the main box of interest is in green and we are told the optizer wants to perform a range scane on the 4 byte primary key. And note the relevant section of the query underneath. The pessimistic rows read estimate is 1,919. If you comapre this to the CLI version, we are receiving pretty much the same information.

Let try something even simpler.
SELECT *
FROM City
WHERE ID = 1200;


Figure 3

Well, we are told that there will be one row read at most (const) but the dad-gum box has turned gray.

What do sub queries look like with VISUAL EXPLAIN?

SELECT Country.name
FROM Country
WHERE Code = (select CountryCode from City where City.ID=1);

Figure 4

VISUAL EXPLAIN looks very promissing as a way to show that the optimzier wants to do with queries and and takes away some of the arcane knowledge needed for the CLI verion. Right now this is a new feature and I am sure it will go through some evolutions. Will it do away with the CLI EXPLAIN? Probably not as sometimes you do need the possible_keys and so many of we dinosuars know it too well to forsake it now. But VISUAL EXPLAIN should grow into a must know tool for MySQL DBAs very quickly.

About these ads