Hi!

Werner> Hi! Werner> This Report was made using the WinMySQLAdmin 1.3 Tool

Werner> 23.05.02 22:21:22

Werner> Description : STRAIGHT_JOIN doesn't respect sequence Werner> How-To-Repeat : Create 2 original tables given by sql files in
upload, Werner> then run 2 EXPLAIN statements on

<cut>

mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM pferde, kunde Werner> WHERE kunde.id = 988 Werner> AND pferde.anbieter = kunde.id Werner> AND verkauft <= 'N' Werner> ORDER BY pferde.datum\G Werner> *************************** 1. row *************************** Werner> table: kunde Werner> type: const Werner> possible_keys: PRIMARY Werner> key: PRIMARY Werner> key_len: 2 Werner> ref: const Werner> rows: 1 Werner> Extra: Using filesort

The above is ok. 'const' tables are always put first, independent if you use STRAIGHT_JOIN or not.

A 'const' table is a table that is read only once at the start of query. After this all fields in the table is regarded as constants.

There is no use of trying to keep the position of this table, even if STRAIGHT_JOIN is used.

<cut>

Werner> Manual 6.4.1.1 JOIN Syntax says:
(http://www.mysql.com/doc/J/O/JOIN.html)

Werner> STRAIGHT_JOIN is identical to JOIN, except that the left table is Werner> always read before the right table. This can be used for those (few) Werner> cases where the join optimiser puts the tables in the wrong order.

From the EXPLAIN part of the manual:

------- 'const': The table has at most one matching row, which will be read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimiser. @code{const} tables are very fast as they are read only once!

-------

The documentation team should update the STRAIGHT_JOIN documentation that it doesn't affect 'const' tables.

Regards, Monty