Re: Join Query Performance Problme

[Rick James]

> I've tried the three sql statements below which end up have the same query plan.
Do on each of them:
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;
The "warnings" will show you what the optimizer turned them into -- possibly identical stuff.

These are not always identical, especially if it is LEFT JOIN:

1. SELECT ... FROM a JOIN b ON a.id=b.id
WHERE ...

2. SELECT ... FROM a JOIN b ON a.id=b.id AND ...

#1 gathers the data from both tables based on the ON condition, _then_ applies the WHERE filtering.
#2 filters as it reaches into b.
Often the two would produce identical results, and the optimizer can safely recognize it.

> the hashed join column value
Hashes are random. Hence, if you have millions of rows, each lookup will be at some random location in the index or table. If the table is too big to be cached in RAM, that will slow down to the disk speed. Rule of Thumb: 100 disk hits per second.

SHOW VARIABLES LIKE '%buffer%';
How much RAM do you have?

> long time to complete ... but the cpu isn't very active
Sounds like I/O-bound.

SHOW CREATE TABLE -- need to see engines and indexes for each table.

Read up on "compound" indexes. You probably need such.