Why mysql Query Sending Data Slow

Question

my query

      SELECT Info.InfoID,
      SessionInfo.SessionInfoID,
      SessionInfo.ANI,
      ANumber.ANumber,
      tmfInfo.PTime,
      tmfInfo.PTry,
      SessionInfo.CardID,
      tmfInfo.Status
 FROM tmfInfo,
      SessionInfo,
      ANumber ,
      ANumberLog,
      ANumberGroup,
      ANumberGroupLog
WHERE (tmfInfo.IVRSessionInfoID = SessionInfo.IVRSessionInfoID)
  AND (SessionInfo.ANumberLogID = ANumber.ANumberLogID)
  AND (ANumber.AccessNumberLogID = ANumberLog.ANumberLogID)
  AND (ANumberrLog.ANumberGroupID = ANumberGroup.ANumberGroupID)
  AND (ANumberGroup.ANumberGroupLogID = ANumberGroupLog.ANumberGroupLogID)
  AND (SessionInfo.SessionCallTime >= '2013-08-01 00:00:00'
  AND (SessionInfo.SessionCallTime <= '2013-08-01 23:59:59')
  AND (ANumberLog.IsDeleted = '0')
  AND (ANumberLog.IsActive = '1')
  AND (ANumberGroupLog.IsDeleted = '0')
  AND (ANumberGroupLog.IsActive = '1')
 ORDER BY SessionInfo.SessionCallTime,tmfInfo.PTime DESC;

it results gives 1237 rows (10 sec)

when iam execute explain cmd

  id    select_type table   type    possible_keys   key key_len ref rows    Extra
   1    SIMPLE  SessionInfo range   PRIMARY,SessionCallTime,ANumberLogID    SessionCallTime 4       57536   Using where; Using temporary; Using filesort
  1       SIMPLE    Anumber ref AnumberLogID    AnumberLogID    5               SessionInfo.ANumberLogID    1   Using where
  1     SIMPLE  AnumberLog  eq_ref  PRIMARY,ANumberGroupID,IsActive,IsDeleted   PRIMARY 4   SessionInfo.ANumberLogID    1   Using where
  1 SIMPLE  AnumberGroup    eq_ref  PRIMARY,ANumberGroupLogID   PRIMARY 4   AnumberLog.ANumberGroupID   1   
  1 SIMPLE  AnumberGroupLog eq_ref  PRIMARY,IsActive,IsDeleted  PRIMARY 4   AnumberGroup.ANumberGroupLogID  1   Using where
 1  SIMPLE  tmfInfo ref IVRSessionInfoID    IVRSessionInfoID    8   SessionInfo.IVRSessionInfoID    1

here query serching more than 57536 rows it will take more time to execute

sending data solw:__

                      Status    Duration
                     starting   0.000009
 Waiting for query cache lock   0.000003
  checking query cache for query    0.000087
          checking permissions  0.000003
         checking permissions   0.000002
         checking permissions   0.000002
         checking permissions   0.000002
         checking permissions   0.000002
         checking permissions   0.000003
                 Opening tables 0.000037
                 System lock    0.000007
 Waiting for query cache lock   0.000053
                        init    0.000039
                  optimizing    0.000018
                  statistics    0.000156
                          preparing 0.000033
           Creating tmp table   0.000029
                   executing    0.000002
          Copying to tmp table  0.353
              Sorting result    0.000238
                Sending data    7.763123
                          end   0.000009
            removing tmp table  0.000011
                           end  0.000004
                       query end    0.000004
                closing tables  0.000014
                freeing items   0.000018
            logging slow query  0.000003
          logging slow query    0.000025
                 cleaning up    0.000005

Do you have indexes on all of the columns you're joining on? — Steven Moseley
– Steven Moseley, Commented Sep 26, 2013 at 10:54
How many records in each table? Do you really need all those joins? — Steven Moseley
– Steven Moseley, Commented Sep 26, 2013 at 10:58

Steven Moseley · Accepted Answer · 2013-09-27 04:14:11Z

1

Try this...

I put the Session info in a derived table subquery to try to limit the number of rows you're joining on tmfInfo.
EDIT: I'm casting the date range to UNIX_TIMESTAMP to improve performance on your timestamp field. THIS IS A BIG ONE!!!!

Here's the query:

SELECT 
    ssnInfo.*
    tmfInfo.PTime,
    tmfInfo.PTry,
    tmfInfo.Status

FROM (
    SELECT
        Info.InfoID,
        SessionInfo.SessionInfoID,
        SessionInfo.ANI,
        ANumber.ANumber,
        SessionInfo.CardID
    FROM
        SessionInfo
        INNER JOIN ANumber AS
            ON ANumber.ANumberLogID = SessionInfo.ANumberLogID,
        INNER JOIN ANumberLog
            ON ANumberLog.ANumberLogID = ANumber.AccessNumberLogID
            AND ANumberLog.IsDeleted = 0 /* Do not put this in quotes unless it's stored as a string */
            AND ANumberLog.IsActive = 1 /* Do not put this in quotes unless it's stored as a string */
        INNER JOIN ANumberGroup
            ON ANumberGroup.ANumberGroupID = ANumberLog.ANumberGroupID
        INNER JOIN ANumberGroupLog
            ON ANumberGroupLog.ANumberGroupLogID = ANumberGroup.ANumberGroupLogID
            AND ANumberGroupLog.IsDeleted = 0 /* Do not put this in quotes unless it's stored as a string */
            AND ANumberGroupLog.IsActive = 1 /* Do not put this in quotes unless it's stored as a string */
    WHERE 
        SessionInfo.SessionCallTime 
            BETWEEN UNIX_TIMESTAMP('2013-08-01 00:00:00') 
            AND UNIX_TIMESTAMP('2013-08-15 23:59:59')
    ) AS ssnInfo
INNER JOIN tmfInfo
    ON ssnInfo.IVRSessionInfoID = tmfInfo.IVRSessionInfoID

edited Sep 27, 2013 at 4:14

answered Sep 26, 2013 at 11:26

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

sudhakar reddy karukala Over a year ago

thanks sir, but iam not getting results with this.. i need ordery by also ORDER BY SessionInfo.SessionCallTime,tmfInfo.PTime DESC;

sudhakar reddy karukala Over a year ago

/* Do not put this in quotes unless it's stored as a string */ this is wrong when iam using quotes '0', i got results

sudhakar reddy karukala Over a year ago

and its taking same time

Steven Moseley Over a year ago

What are the data types for ANumberLog.IsDeleted and SessionInfo.SessionCallTime

sudhakar reddy karukala Over a year ago

enum('0','1') & timestamp

|

Collectives™ on Stack Overflow

Why mysql Query Sending Data Slow

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related