Hello all,
Need an explanation on this:
SELECT col1, col2, ...
FROM tbl1 JOIN tbl2 ON (some cond)
JOIN tbl3 ON (some cond)
.
.
.
WHERE
col1 > <something> AND col2 > <something> etc...
Running an EXPLAIN on the above shows that the join type is ALL. I came to know
that MySQL is not able to use any INDEX for the above query. The query is such
that it does not require any search on PRIMARY KEY and that is why it is not
used in the WHERE clause.
To optimize this query I did the following:
SELECT col1, col2, ...
FROM tbl1 JOIN tbl2 ON (some cond)
JOIN tbl3 ON (some cond)
.
.
.
WHERE
col1 > <something> AND col2 > <something> etc...
AND PRIMARYKEY > 0;
Now running an EXPLAIN showed join type as range and showed that it had to scan
half the number of rows less than the previous time. It Was using PRIMARYKEY
column as the INDEX this time.
I need an explanation of whether what I did is an optimization or not? Or
should i be looking into something else to actually optimize the query.
Thanks
Ratheesh Bhat K J
