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
1745
February 01, 2012 06:51AM
926
February 01, 2012 11:14AM
934
February 02, 2012 12:39AM
908
February 02, 2012 06:47AM
916
February 02, 2012 07:02PM
905
February 03, 2012 12:29AM
1149
February 03, 2012 11:27AM
912
February 04, 2012 10:39AM
929
February 06, 2012 06:13AM
1086
February 14, 2012 06:08PM
Re: Join Query Performance Problme
934
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.