mysql, reading explain statement
i have query starting means pleasantness application. i'm perplexing know explain matter improved know where indexes potentially missing:
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+
| id | select_type | list | form | possible_keys | pivotal | key_len | ref | rows | additional |
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+
| 1 | rudimentary | s | ref | client_id | client_id | 4 | const | 102 | controlling temporary; controlling filesort |
| 1 | rudimentary | u | eq_ref | primary | primary | 4 | www_foo_com.s.user_id | 1 | |
| 1 | rudimentary | | ref | session_id | session_id | 4 | www_foo_com.s.session_id | 1 | controlling index |
| 1 | rudimentary | h | ref | email_id | email_id | 4 | www_foo_com.a.email_id | 10 | controlling index |
| 1 | rudimentary | ph | ref | session_id | session_id | 4 | www_foo_com.s.session_id | 1 | controlling index |
| 1 | rudimentary | em | ref | session_id | session_id | 4 | www_foo_com.s.session_id | 1 | |
| 1 | rudimentary | pho | ref | session_id | session_id | 4 | www_foo_com.s.session_id | 1 | |
| 1 | rudimentary | c | | userfield | zero | zero | zero | 1108 | |
+----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+
8 rows set (0.00 sec)
i'm perplexing know where indexes blank reading explain statement. satisfactory contend know optimize query but observant query during only feeling during law explain?
it appears indicate opposing 'c' list achilles heel. what's best proceed index formed consistent values permitted mysql's documentation? |
note, i also total an index userfield cdr list hasn't finished many good either.
thanks.
--- correct ---
here's query, contemptible -- don't know since i neglected consolidate initial 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 s.`user_id`=u.`user_id`
left join `fa_email_aliases` a.session_id = s.session_id
left join `fa_email_headers` h h.email_id = a.email_id
left join `fa_phones` ph ph.session_id = s.session_id
left join `fa_email_aliases` em em.session_id = s.session_id em.status = 1
left join `fa_phones` pho pho.session_id = s.session_id pho.status = 1
left join `cdr` c c.userfield = ph.phone_id
where s.`partner_id`=1
organisation s.`session_id`
Comments
Post a Comment