Oracle 5.0, Reference Manual

Page: 671 / 3232

EXPLAIN
Output Format
651
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
For this example, make the following assumptions:
• The columns being compared have been declared as follows.
Table Column Data Type
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
• The tables have the following indexes.
Table Index
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID
(primary key)
do CUSTNMBR
(primary key)
• The
tt.ActualPC
values are not evenly distributed.
Initially, before any optimizations have been performed, the
EXPLAIN
statement produces the following
information:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872
ClientID,
ActualPC
Range checked for each record (index map: 0x23)
Because
type
is
ALL
for each table, this output indicates that MySQL is generating a Cartesian
product of all the tables; that is, every combination of rows. This takes quite a long time, because the
product of the number of rows in each table must be examined. For the case at hand, this product is 74
× 2135 × 74 × 3872 = 45,268,558,720 rows. If the tables were bigger, you can only imagine how long it
would take.
One problem here is that MySQL can use indexes on columns more efficiently if they are declared
as the same type and size. In this context,
VARCHAR
and
CHAR
are considered the same if they are
declared as the same size.
tt.ActualPC
is declared as
CHAR(10)
and
et.EMPLOYID
is
CHAR(15)
,
so there is a length mismatch.
To fix this disparity between column lengths, use
ALTER TABLE
to lengthen
ActualPC
from 10
characters to 15 characters: