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