List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 25 2003 5:05pm
Subject:Re: Optimize Question
View as plain text  
In the last episode (Aug 25), Patrick Crowley said:
> 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.)

An index on t (publish,photo,name_present,location_city) would be the
most effiecient, I think.

> **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     |keylen| 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          |
> +---------------+---------------+

-- 
	Dan Nelson
	dnelson@stripped
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