Geek Answers Handbook

MySQL reading this EXPLAIN statement

I have a query that is starting to cause some concern in my application. I am trying to understand this EXPLAIN statement better in order to understand where the indexes are potentially missing:

            + ---- + ------------- + ------- + -------- + ------------- - + ------------ + --------- + ------------------------ ------- + ------ + --------------------------------- +
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            + ---- + ------------- + ------- + -------- + ------------- - + ------------ + --------- + ------------------------ ------- + ------ + --------------------------------- +
            | 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 | a | 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 | ALL | userfield | NULL | NULL | NULL | 1108 | |
            + ---- + ------------- + ------- + -------- + ------------- - + ------------ + --------- + ------------------------ ------- + ------ + --------------------------------- +
            8 rows in set (0.00 sec)

I am trying to figure out where my indexes are missing by reading this EXPLAIN statement. Is it fair to say that you can understand how to optimize this query without seeing the query at all, and just look at the EXPLAIN results?

It seems that ALL scans on table "c" are the Achilles heel. What is the best way to index this based on constant values, as recommended in the MySQL documentation? |

Notice, I also added an index to the userfield field in the cdr table, and this also did not bring much results.

Thanks.

--- change ---

Here is a request, sorry - I do not know why I neglected to include it in the first pass.

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
           FROM `fa_sessions` s
           LEFT JOIN `fa_users` u ON s.`user_id`=u.`user_id`
           LEFT JOIN `fa_email_aliases` a 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 AND em.status = 1
           LEFT JOIN `fa_phones` pho ON pho.session_id = s.session_id AND pho.status = 1
           LEFT JOIN `cdr` c ON c.userfield = ph.phone_id
           WHERE s.`partner_id`=1
           GROUP BY s.`session_id`
+5
source share
4 answers

, , , . , , . filesort. .

:

filesort

MySQL , . , WHERE. . . 7.2.12, "ORDER BY Optimization".

, MySQL . , GROUP BY ORDER BY -.

, .

+3

?

2 .

Reasonning

, c . , , : , , .

MySQL, . , , c .

, , , , , . ( , ). - 90% , .

, , . , SQL, , .

.

MySQL EXPLAIN

+3

, , , , , - ALL , . (userfield) c, , , , .

+2

, , , , :

  • sessions, partner_id=1, , partner_id,
  • sessions users, user_id
  • sessions phones, status=1, session_id , , status
  • sessions phones, session_id phone_id **
  • phones cdr userfield
  • sessions email_aliases, status=1 session_id , , status
  • sessions email_aliases, session_id email_id **
  • email_aliases email_headers email_id

**, 2 , session_id phone_id email_id . .

, :

:

fa_sessions ( partner_id, session_id )  
fa_users ( user_id )  
fa_email_aliases ( session_id, email_id )  
fa_email_headers ( email_id )  
fa_email_aliases ( session_id, status )  
fa_phones ( session_id, status, phone_id ) 
cdr ( userfield )

  • You will almost certainly get acceptable performance without creating all of these.
  • If any of the tables is small (less than 100 rows), then probably you should not create an index.
  • fa_email_aliasesmay work with ( session_id, status, email_id ), depending on how the optimizer works.
+1
source

More articles:


All Articles