1

enter image description here

Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?

2 Answers 2

2

ref evidently means a keyed lookup because the definition of ref in EXPLAIN is

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

As for ALL, my guess is that refers to a full table scan because the same URL says

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

In all honesty, I cannot tell what the ORDER BY is. You should try comparing that graph above to the text-based output from one of these:

and see if the JOIN ordering makes sense there.

1

Join ordering is left to right. That is, in the above example a nested-loop join is performed by a full table scan of CUSTOMER followed by index look-ups into ORDERS and LINEITEM. (The meaning of ALL and ref are defined in the user manual for traditional EXPLAIN.)

Note that Visual EXPLAIN is significantly updated in MySQL Workbench 6.1 including more descriptive text for the table operations.

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.