Hi.
On Sun, Feb 04, 2001 at 02:57:06PM -0700, [EMAIL PROTECTED] wrote:
> I recently asked for some help with join order and it's now fixed, kinda :)
>
> On a web server running mySQL version 3.22.32 on a Linux box (cobalt RAQ) I get the
>following output for this query:
>
> EXPLAIN SELECT * FROM (LOG_LOG LEFT JOIN SAT_CMD ON LOG_LOG.LOG_ID=SAT_CMD.LOGID)
>LEFT JOIN SAT_GEN ON LOG_LOG.LOG_ID=SAT_GEN.LOGID;
>
> table type possible_keys key key_len ref rows Extra
> LOG_LOG ALL NULL NULL NULL NULL 41
> SAT_CMD ALL LOGID NULL NULL NULL 7
> SAT_GEN eq_ref LOGID LOGID 8 LOG_LOG.LOG_ID 1
That shouldn't be that way even with 3.22.x. The only reason I know
for this kind of behaviour is when the columns are not of exactly the
same type (I mean LOG_ID/LOGID).
> As you can see it's not an optimal query. Nothing I did could fix it.
>
> So I dumped the tables and put them on my local machine running mySQL version
>3.23.28-gamma on Win98. This is the output I get now for the same query:
>
>
>+---------+--------+---------------+-------+---------+----------------+------+-------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
>|
>
>+---------+--------+---------------+-------+---------+----------------+------+-------+
> | log_log | ALL | NULL | NULL | NULL | NULL | 41 | |
> | sat_cmd | eq_ref | LOGID | LOGID | 8 | log_log.log_id | 1 | |
> | sat_gen | eq_ref | LOGID | LOGID | 8 | log_log.log_id | 1 | |
>
>+---------+--------+---------------+-------+---------+----------------+------+-------+
> 3 rows in set (0.55 sec)
>
> All fields remained the same except for case and all indexes are the same.
>
> Is it the different versions causing this or different platforms?
Very probably a deficiency in 3.22.x has been fixed meanwhile. That's
the reason why one should always test with the most recent version if
a problem is still reproducible (or fixed).
Bye,
Benjamin.
---------------------------------------------------------------------
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
