List:General Discussion« Previous MessageNext Message »
From:Patrick Crowley Date:August 25 2003 4:42pm
Subject:Optimize Question
View as plain text  
Hi all!

I'm trying to optimize the statement below, but I can't figure out why this
query is still doing an ALL join with a filesort.

There are several indexes on both my main table ('t'), and all the tables
I'm trying to join. (I also tried adding an index for t.publish, which
bumped my join type to 'range' but still gave me a 'filesort' and seemed to
produce slower query results.)

What can I do to further optimize?

Thanks,
Patrick

.......

**SELECT STATEMENT**

SELECT
t.id,
t.name_present,
t.location_city,
t.photo,
a.name_short,
a.name_long,
b.name as b_name,
c.name as c_name,
d.id as d_id,
d.name as d_name

FROM t

LEFT JOIN a
ON t.a_id = a.id
LEFT JOIN b
ON t.b_id = b.id
LEFT JOIN c
ON t.c_id = c.id
LEFT JOIN d
ON t.d_id = d.id
LEFT JOIN e_t
ON e_t.t_id = t.id
LEFT JOIN e
ON e_t.e_id = e.id

WHERE  t.publish = 'Yes' AND e.id = '2'
ORDER  BY t.photo, t.name_present, t.location_city
LIMIT  0,25

**EXPLAIN RESULTS FOR THIS QUERY**
+-----+--------+---------+---------+----------+------+---------------------+
| tbl | type   | key     | key_len | ref      | rows | Extra               |
+-----+--------+---------+---------+----------+------+---------------------+
| t   | ALL    | NULL    |    NULL | NULL     | 4279 | where used; filesort|
| a   | eq_ref | PRIMARY |       2 | t.a_id   |    1 |                     |
| b   | eq_ref | PRIMARY |       2 | t.b_id   |    1 |                     |
| c   | eq_ref | PRIMARY |       1 | t.c_id   |    1 |                     |
| d   | eq_ref | PRIMARY |       1 | t.d_id   |    1 |                     |
| e   | eq_ref | PRIMARY |       1 | e_t.e_id |    1 | where used; index   |
| e_t | ref    | t_id    |       2 | t.id     |    1 |                     |
+-----+--------+---------+---------+----------+------+---------------------+
('possible_keys' was identical to 'key', but i removed it due to space
issues)

**INDEXES ON 't'**
+---------------+---------------+
| Key_name      | Column_name   |
+---------------+---------------+
| PRIMARY       | id            |
| location_city | location_city |
| name_present  | name_present  |
| a_id          | a_id          |
| b_id          | b_id          |
| c_id          | c_id          |
| d_id          | d_id          |
+---------------+---------------+

Thread
Optimize QuestionPatrick Crowley25 Aug
  • Re: Optimize QuestionDan Nelson25 Aug
    • Re: Optimize QuestionPatrick Crowley25 Aug
      • Re: Optimize QuestionDan Nelson25 Aug
        • Re: Optimize QuestionPatrick Crowley25 Aug
          • Re: Optimize QuestionDan Nelson25 Aug