sql - MySQL, reading this EXPLAIN statement -

i have query starting cause concern in application. i'm trying understand explain statement better understand indexes potentially missing:

             +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+             | id | select_type | table | type   | possible_keys | key        | key_len | ref                           | rows |                           |             +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+             |  1 | simple      | s     | ref    | client_id     | client_id  | 4       | const                         |  102 | using temporary; using filesort |             |  1 | simple      | u     | eq_ref | primary       | primary    | 4       | www_foo_com.s.user_id         |    1 |                                 |             |  1 | simple      |     | ref    | session_id    | session_id | 4       | www_foo_com.s.session_id      |    1 | using index                     |             |  1 | simple      | h     | ref    | email_id      | email_id   | 4       | www_foo_com.a.email_id        |   10 | using index                     |             |  1 | simple      | ph    | ref    | session_id    | session_id | 4       | www_foo_com.s.session_id      |    1 | using index                     |             |  1 | simple      | em    | ref    | session_id    | session_id | 4       | www_foo_com.s.session_id      |    1 |                                 |             |  1 | simple      | pho   | ref    | session_id    | session_id | 4       | www_foo_com.s.session_id      |    1 |                                 |             |  1 | simple      | c     |    | userfield     | null       | null    | null                          | 1108 |                                 |             +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+             8 rows in set (0.00 sec) 

i'm trying understand indexes missing reading explain statement. fair 1 can understand how optimize query without seeing query @ , @ results of explain?

it appears scan against 'c' table achilles heel. what's best way index based on constant values recommended on mysql's documentation? |

note, added index userfield in cdr table , hasn't done either.

thanks.

--- edit ---

here's query, sorry -- don't know why neglected include first pass through.

select s.`session_id` id,                   date_format(s.`created`,'%m/%d/%y') date,                   u.`name`,                   count(distinct c.id) calls,                   count(distinct h.id) emails,                   sec_to_time(max(distinct c.duration)) duration,                   (count(distinct em.email_id) + count(distinct pho.phone_id) > 0) status            `fa_sessions` s            left join `fa_users` u on s.`user_id`=u.`user_id`            left join `fa_email_aliases` on a.session_id = s.session_id            left join `fa_email_headers` h on h.email_id = a.email_id            left join `fa_phones` ph on ph.session_id = s.session_id            left join `fa_email_aliases` em on em.session_id = s.session_id , em.status = 1            left join `fa_phones` pho on pho.session_id = s.session_id , pho.status = 1            left join `cdr` c on c.userfield = ph.phone_id            s.`partner_id`=1            group s.`session_id`       

i assume you've looked here more info telling you. means going through of them. using temporary , using filesort talked on page. might want @ that.

from page:

using filesort

mysql must pass find out how retrieve rows in sorted order. sort done going through rows according join type , storing sort key , pointer row rows match clause. keys sorted , rows retrieved in sorted order. see section 7.2.12, “order optimization”.

using temporary

to resolve query, mysql needs create temporary table hold result. typically happens if query contains group , order clauses list columns differently.

i agree seeing query might figure things out better.