The EXPLAIN statement can also be applied to joins. It shows:
- the sequence in which the tables will be processed when the select statement is performed,
- whether the rows of a new table can be accessed directly or via an index, starting from the join column values of the old temporary result table,
- the strategy according to which the corresponding new table is searched through when the rows of this table cannot be accessed directly or via an index.
The following strategies can be applied to access rows of the new table starting from the join column values of the old temporary result table:
- JOIN VIA KEY COLUMN
The join column is the only key column. Table rows of the new table are accessed directly.
- JOIN VIA KEY RANGE
The join column whose name is displayed is the first key column. Within the range of keys, table rows of the new table are accessed sequentially.
- JOIN VIA INDEXED COLUMN
The join column is a single-column indexed column. Access is made via the index of the column whose name is displayed.
- JOIN VIA MULTIPLE KEY COLUMNS
The specified join columns can be combined to form the key of the new table. This key consists of several columns. The table rows of the new table are accessed directly.
- JOIN VIA RANGE OF MULTIPLE KEY COLUMNS
The specified join columns can be combined to form the initial part of the key of the new table. This key consists of several columns. Within the range of keys, the table rows of the new table are accessed sequentially.
- JOIN VIA MULTIPLE INDEXED COLUMNS
The specified join columns can be combined to form a complete multiple-column index. Access is made via this index.
- JOIN VIA RANGE OF MULTIPLE INDEXED COL.
The specified join columns can be combined to form the initial part of the index consisting of several columns. Within the index range, the table rows are accessed.
Comment on the multiple-column key strategies or index strategies:
If the column lengths of the two columns to be compared within a join step are not equal, these strategies cannot be utilized. To bypass this restriction, it is recommended to use the same domain for the definition of the colums to be joined.
Examples:
The EXPLAIN statement causes the following output to be made:
