code_junkie

MySQL, reading this EXPLAIN statement

Nov 11th, 2011
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.15 KB | None | 0 0
  1. SELECT s.`session_id` id,
  2. DATE_FORMAT(s.`created`,'%m/%d/%Y') date,
  3. u.`name`,
  4. COUNT(DISTINCT c.id) calls,
  5. COUNT(DISTINCT h.id) emails,
  6. SEC_TO_TIME(MAX(DISTINCT c.duration)) duration,
  7. (COUNT(DISTINCT em.email_id) + COUNT(DISTINCT pho.phone_id) > 0) status
  8. FROM `fa_sessions` s
  9. LEFT JOIN `fa_users` u ON s.`user_id`=u.`user_id`
  10. LEFT JOIN `fa_email_aliases` a ON a.session_id = s.session_id
  11. LEFT JOIN `fa_email_headers` h ON h.email_id = a.email_id
  12. LEFT JOIN `fa_phones` ph ON ph.session_id = s.session_id
  13. LEFT JOIN `fa_email_aliases` em ON em.session_id = s.session_id AND em.status = 1
  14. LEFT JOIN `fa_phones` pho ON pho.session_id = s.session_id AND pho.status = 1
  15. LEFT JOIN `cdr` c ON c.userfield = ph.phone_id
  16. WHERE s.`partner_id`=1
  17. GROUP BY s.`session_id`
  18.  
  19. SELECT s.`session_id` id,
  20. DATE_FORMAT(s.`created`,'%m/%d/%Y') date,
  21. u.`name`,
  22. COUNT(DISTINCT c.id) calls,
  23. COUNT(DISTINCT h.id) emails,
  24. SEC_TO_TIME(MAX(DISTINCT c.duration)) duration,
  25. (COUNT(DISTINCT em.email_id) + COUNT(DISTINCT pho.phone_id) > 0) status
  26. FROM `fa_sessions` s
  27. LEFT JOIN `fa_users` u ON s.`user_id`=u.`user_id`
  28. LEFT JOIN `fa_email_aliases` a ON a.session_id = s.session_id
  29. LEFT JOIN `fa_email_headers` h ON h.email_id = a.email_id
  30. LEFT JOIN `fa_phones` ph ON ph.session_id = s.session_id
  31. LEFT JOIN `fa_email_aliases` em ON em.session_id = s.session_id AND em.status = 1
  32. LEFT JOIN `fa_phones` pho ON pho.session_id = s.session_id AND pho.status = 1
  33. LEFT JOIN `cdr` c ON c.userfield = ph.phone_id
  34. WHERE s.`partner_id`=1
  35. GROUP BY s.`session_id`
  36.  
  37. fa_sessions ( partner_id, session_id )
  38. fa_users ( user_id )
  39. fa_email_aliases ( session_id, email_id )
  40. fa_email_headers ( email_id )
  41. fa_email_aliases ( session_id, status )
  42. fa_phones ( session_id, status, phone_id )
  43. cdr ( userfield )
Add Comment
Please, Sign In to add comment