Optimizing MySQL - Database Design Manual: Using MySQL for Windows - page 151

Figure 13.4 An inefficient cross join.

Figure 13.4 shows the output of cross joining the log, cookies, webpage and visitorbook
tables together. If you are performing a cross join without knowledge of the number of rows
in your tables, it is useful to use EXPLAIN and view the rows column to estimate the num-
ber of rows you may get returned. Figure 13.5 shows the EXPLAIN running on this query as
follows:

EXPLAIN SELECT *
FROM log, cookies, webpage, visitorbook

All of rows column values multiplied together (7 × 6 × 6 × 7) comes to 1764, which is the

same as the number of rows returned in Figure 13.4.
We have already discussed how cross joins are inefficient; to demonstrate, we will run the
query above with a WHERE clause to restrict the data that we are obtaining from the query
as follows:

SELECT *
FROM webpage, log, cookies, visitorbook
WHERE cookies.cookieid > 3

Figure 13.5 EXPLAIN on the cross join.