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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
