Hi!
>>>>> "Eric" == Eric DeCosta <[EMAIL PROTECTED]> writes:
>> Description:
Eric> Index analysis and optimization is choosing wrong index when a
Eric> table involved in a JOIN is compound. MySQL should still be using
Eric> the first part of the GECK_STATE PRIMARY key (geck_id) since it is the column
being
Eric> used to JOIN on the GECK table.
>> How-To-Repeat:
Eric> You should already have edecosta.mathworks.com.gz on your ftp site. If not,
contact me
Eric> and I will up-load again.
<cut>
Exactly what commands did you do between the two EXPLAIN ?
It looks like your did more than just modify GEEK_STATE, as the number
of rows from COMPONENT also changes between the queries:
EXPLAIN1
Eric> | COMPONENT | ref | PRIMARY,component | component | 32 | const
| 1 | where used; Using temporary; Using filesort |
EXPLAIN2
Eric> | COMPONENT | ref | PRIMARY,component | component | 32 | const
| 7 | where used; Using temporary; Using filesort |
If we take a closer look at the explain results:
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
| COMPONENT | ref | PRIMARY,component | component | 32 | const
| | 1 | where used; Using temporary; Using filesort |
| STATUS | ALL | PRIMARY,status | NULL | NULL | NULL
| | 18 | where used |
| GECK_STATE | ref | PRIMARY,owner,status_id | status_id | 1 |
|STATUS.status_id | 5567 | |
| U1 | eq_ref | PRIMARY | PRIMARY | 2 |
|GECK_STATE.owner | 1 | where used |
| GECK | eq_ref | PRIMARY,component_id | PRIMARY | 4 |
|GECK_STATE.geck_id | 1 | where used |
+------------+--------+-------------------------+-----------+---------+--------------------+------+---------------------------------------------+
and:
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
| COMPONENT | ref | PRIMARY,component | component | 32 | const
| | 7 | where used; Using temporary; Using filesort |
| GECK | ref | PRIMARY,component_id | component_id | 2 |
|COMPONENT.component_id | 436 | |
| GECK_STATE | eq_ref | PRIMARY,owner,status_id | PRIMARY | 4 |
|GECK.geck_id | 1 | |
| STATUS | eq_ref | PRIMARY,status | PRIMARY | 1 |
|GECK_STATE.status_id | 1 | where used |
| U1 | eq_ref | PRIMARY | PRIMARY | 2 |
|GECK_STATE.owner | 1 | where used |
+------------+--------+-------------------------+--------------+---------+------------------------+------+---------------------------------------------+
you can see that the tables comes in different order for the two
queries. As in the first case, GECK_STATE is used before GECK, MySQL
can't use the PRIMARY KEY index to find the rows in GECK_STATE.
The problem here is not how the keys are used, but why the table-join
optimizer produces a different order for the tables.
The join optimizer tries to optimize the products of the 'rows' columns. The
question is why the first query goes wrong.
One reason is probably that as you had a in the first case a key on
'geck_id', required_by' , MySQL couldn't know accurately enough how
many rows would be matched by only 'geck_id'.
You could have supplied MySQL with this information by doing 'analyze
table GEEK_STATE'. Did you try to do that ?
You can also force the optimizer to use the table in a specific order,
when the estimated numbers of rows are not good close enough to the
truth.
In this case you could say: in the FROM clause:
FROM GECK STRAIGHT_JOIN GECK_STATE, USER U1, COMPONENT, STATUS
which would force MySQL to put GECK before GECK_STATE in the table,
which could help you to get a better responce time.
To be able to know exactly where things went wrong, I would need
access to all the tables in the original query.
Regards,
Monty
---------------------------------------------------------------------
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
