4 messages in com.mysql.lists.mysqlRe: Optimize ORDER BY in join (MySQL ...| From | Sent On | Attachments |
|---|---|---|
| SGr...@unimin.com | 30 Aug 2004 10:18 | |
| Michael Stassen | 30 Aug 2004 10:48 | |
| Lorderon | 30 Aug 2004 11:12 | |
| Lorderon | 30 Aug 2004 11:44 |
| Subject: | Re: Optimize ORDER BY in join (MySQL v4.1) |
|---|---|
| From: | Michael Stassen (Mich...@verizon.net) |
| Date: | 08/30/2004 10:48:00 AM |
| List: | com.mysql.lists.mysql |
EXPLAIN is telling you that it has to look at all 1000 rows of tbl_2 to satisfy your JOIN, because no index will do the job (key and key_len are both NULL). It is not telling you that it is doing the filesort first (mysql is smarter than that). See the manual for details on reading the output of EXPLAIN <http://dev.mysql.com/doc/mysql/en/EXPLAIN.html>.
Without seeiing the actual query and the indexes on the tables it is hard to say, but I believe the solution lies in the answers to these questions: What column is used to join tbl_2? What column in tbl_2 is used for sorting? What indexes are on tbl_2?
Michael
Lorderon wrote:
Hello,
[ This runs on MySQL version 4.1.3-beta-nt ]
I have a query using INNER JOIN on some tables, lets say tbl_1,tbl_2,tbl_3. I restrict the result using WHERE on columns from tbl_1 and tbl_3. The total number of rows returned is 50. I order the result with columns from tbl_2. The table tbl_2 has 1,000 rows.
Doing EXPLAIN returns this: | id | type | table | type | possible_keys | key | key_len | ref | rows | Extra | | 1 | SIMPLE | tbl_2 | ALL | PRIMARY | NULL | NULL | NULL | 1000 | Using filesort | | 2 | SIMPLE | tbl_1 | ref | PRIMARY | PRIMARY | 8 | tbl_2.id | 1 | Using where | | 3 | SIMPLE | tbl_3 | ref | PRIMARY | PRIMARY | 8 | tbl_2.id | 1 | Using index |
The problem in this is that the filesort algorithm is going over all the 1000 rows of tbl_2 ! The filesort algorithm says that it skips over rows that do not match into the result, so... Why doesn't the filesort go only over the 50 rows returned by the restriction ? Is this a bug that should be resolved in the released version?
-thanks, Lorderon.