Hello,
Just wanted to talk about the MySql Query Optimiser.
After having performance problems with some queries, I arrived to
thoses conclusions:
- The order of joins with constants and column have no effect on the
speed of the query
- MySql make all joins between tables before extracting the required
rows
table t1
+---+---+
|num|let|
+---+---+
| 1 | A |
| 2 | A |
| 3 | B |
+---+---+
table t2
+---+---+
|num|let|
+---+---+
| 1 | A |
| 2 | B |
+---+---+
If I do something like :
select t1.num
from t1,t2
where t2.let='A'
and t1.let=t2.let
MySql seem's make in memory a table like :
+------+------+------+------+
|t1.num|t1.let|t2.num|t2.let|
+------+------+------+------+
| 1 | A | 1 | A |
| 2 | A | 1 | A |
| 3 | B | 2 | B |
+------+------+------+------+
and then extract the first and the second lines
while it could have first extract the first line of the table t2
and then make all the required joins.
That would have done in memory :
+------+------+------+------+
|t1.num|t1.let|t2.num|t2.let|
+------+------+------+------+
| 1 | A | 1 | A |
| 2 | A | 1 | A |
+------+------+------+------+
I don't remeber cause I've not played with Oracle from several month,
but I think Oracle was doing like this.
Could you tell me if such an Optimiser is planned with MySql 4 ??
Maybe such an Optimiser is useless ? (when we'll have sub-queries
we'll be able to restrict the number of joins in memory)
ps: MySql 4.0.0 Alpha work fine on Amiga ;)
--
Best regards,
SixK
//
\\ // Amiga spirit will never die
\\/
mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
