MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join Query Performance Problme
Posted by: Rick James
Date: February 03, 2012 10:18AM

> 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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2103
February 01, 2012 06:51AM
1121
February 01, 2012 11:14AM
1144
February 02, 2012 12:39AM
1114
February 02, 2012 06:47AM
1147
February 02, 2012 07:02PM
1112
February 03, 2012 12:29AM
1356
February 03, 2012 11:27AM
1138
February 04, 2012 10:39AM
1123
February 06, 2012 06:13AM
1274
February 14, 2012 06:08PM
Re: Join Query Performance Problme
1126
February 03, 2012 10:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.