mysql - Query runs fast, but stored procedure is slow - Tried multiple things

Question

I have a stored procedure which queries values from 2 tables (message and message_archived) which contain many data.

CREATE PROCEDURE `get_all_message_report`(IN fromDate TIMESTAMP, IN toDate TIMESTAMP, IN period VARCHAR(15))

BEGIN

DECLARE lfromDate TIMESTAMP;
DECLARE ltoDate TIMESTAMP;
DECLARE lperiod VARCHAR(15);
DECLARE dateFormat VARCHAR(15);

SET lfromDate = fromDate;
SET ltoDate = toDate;
SET lperiod = period;
SET dateFormat = "";

IF lperiod = "monthly"
THEN
  SET dateFormat = '%b';
ELSEIF lperiod = "daily"
  THEN
    SET dateFormat = '%Y-%c-%d';
ELSEIF lperiod = "weekly"
  THEN
    SET dateFormat = '%x-%v';
END IF;

CREATE TABLE tempMessages(
  id bigint(20),
  generated_time timestamp,
  direction varchar(255),
  status varchar(255),
  read_status varchar(50),
  type varchar(255),
  from_number varchar(255),
  to_number varchar(255),
  INDEX ind (id,generated_time)
);

INSERT INTO tempMessages(id,generated_time,direction,status,read_status,type,from_number,to_number)
SELECT id,generated_time, direction, status, read_status, type, from_number, to_number
FROM (
     SELECT id,generated_time, direction, status, read_status, type, from_number, to_number
     FROM message WHERE generated_time BETWEEN lfromDate AND ltoDate

     union all

     SELECT id,generated_time, direction, status, read_status, type, from_number, to_number
     FROM message_archived WHERE generated_time BETWEEN lfromDate AND ltoDate
     ) t;

SELECT
  sentMessageTable.date,
  IFNULL(sentMessageTable.sentcount, 0)              AS sentMessageCount,
  IFNULL(newPassMessagesTable.newpassmessagescount, 0)       AS newPassMessagesCount,
  IFNULL(newReviewMessagesTable.newreviewmessagescount, 0)       AS newReviewMessagesCount,
  IFNULL(newFailMessagesTable.newfailmessagescount, 0)       AS newFailMessagesCount,
  IFNULL(respondedPassThreadsTable.respondedpassthreadcount, 0)       AS passThreadsRespondedCount,
  IFNULL(respondedReviewThreadsTable.respondedreviewthreadcount, 0)       AS reviewThreadsRespondedCount,
  IFNULL(respondedFailThreadsTable.respondedfailthreadcount, 0)       AS failThreadsRespondedCount
FROM
  (
      (

        /*  sent message count*/
        SELECT
          date,
          sum(sentcount1) AS sentcount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date,
              COUNT(*)                                AS sentcount1
            FROM tempMessages
            WHERE direction = 'outgoing' and status in ('SENT','DELIVERED')
            GROUP BY date
          ) t
        GROUP BY date
        ORDER BY date
      ) AS sentMessageTable LEFT JOIN

      /* new pass messages count */
      (
        SELECT
         date1,
         sum(newpassmessagescount1) AS newpassmessagescount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date1,
              COUNT(*)                                AS newpassmessagescount1
            FROM tempMessages
            WHERE direction='incoming'
                  AND read_status='UNREAD' AND type='PASS'
            GROUP BY date1
          ) t
        GROUP BY date1
        ORDER BY date1
      )
      AS newPassMessagesTable
      ON sentMessageTable.date=newPassMessagesTable.date1
      LEFT JOIN

      /* new review messages count */
      (
        SELECT
         date2,
         sum(newreviewmessagescount1) AS newreviewmessagescount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date2,
              COUNT(*)                                AS newreviewmessagescount1
            FROM tempMessages
            WHERE direction='incoming'
                  AND read_status='UNREAD' AND type='REVIEW'
            GROUP BY date2
          ) t
        GROUP BY date2
        ORDER BY date2
      )
      AS newReviewMessagesTable
      ON sentMessageTable.date=newReviewMessagesTable.date2
      LEFT JOIN

      /* new fail messages count */
      (
        SELECT
         date3,
         sum(newfailmessagescount1) AS newfailmessagescount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date3,
              COUNT(*)                                AS newfailmessagescount1
            FROM tempMessages
            WHERE direction='incoming'
                  AND read_status='UNREAD' AND type='FAIL'
            GROUP BY date3
          ) t
        GROUP BY date3
        ORDER BY date3
      )
      AS newFailMessagesTable
      ON sentMessageTable.date=newFailMessagesTable.date3
      LEFT JOIN

      /*    responded pass thread count*/
      (
        SELECT
          date4,
          count(*) AS respondedpassthreadcount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date4,
              from_number,
              to_number,
              COUNT(*),
              MAX(msg.id)                             AS maxId
            FROM tempMessages msg
            GROUP BY date4, msg.from_number, msg.to_number
            HAVING maxId IN
                   (
                     SELECT max(msg.id)
                     FROM tempMessages msg
                     WHERE msg.direction = "outgoing"
                     GROUP BY DATE_FORMAT(generated_time, dateFormat), msg.from_number, msg.to_number
                   ) AND maxId NOT IN (SELECT MIN(msgSub.id)
                                       FROM tempMessages msgSub
                                       GROUP BY msgSub.from_number, msgSub.to_number
                                       HAVING MIN(msgSub.id) = MAX(msgSub.id))
                     AND from_number IN ( select msg1.to_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='PASS')
                     AND to_number IN ( select msg1.from_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='PASS')
          ) t
        GROUP BY date4
        ORDER BY date4
      ) AS respondedPassThreadsTable
        ON sentMessageTable.date = respondedPassThreadsTable.date4
      LEFT JOIN

      /*    responded review thread count*/
      (
        SELECT
          date5,
          count(*) AS respondedreviewthreadcount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date5,
              from_number,
              to_number,
              COUNT(*),
              MAX(msg.id)                             AS maxId
            FROM tempMessages msg
            GROUP BY date5, msg.from_number, msg.to_number
            HAVING maxId IN
                   (
                     SELECT max(msg.id)
                     FROM tempMessages msg
                     WHERE msg.direction = "outgoing"
                     GROUP BY DATE_FORMAT(generated_time, dateFormat), msg.from_number, msg.to_number
                   ) AND maxId NOT IN (SELECT MIN(msgSub.id)
                                       FROM tempMessages msgSub
                                       GROUP BY msgSub.from_number, msgSub.to_number
                                       HAVING MIN(msgSub.id) = MAX(msgSub.id))
                     AND from_number IN ( select msg1.to_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='REVIEW')
                     AND to_number IN ( select msg1.from_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='REVIEW')
          ) t
        GROUP BY date5
        ORDER BY date5
      ) AS respondedReviewThreadsTable
        ON sentMessageTable.date = respondedReviewThreadsTable.date5
      LEFT JOIN

      /*responded fail thread count*/
      (
        SELECT
          date6,
          count(*) AS respondedfailthreadcount
        FROM
          (
            SELECT
              DATE_FORMAT(generated_time, dateFormat) AS date6,
              from_number,
              to_number,
              COUNT(*),
              MAX(msg.id)                             AS maxId
            FROM tempMessages msg
            GROUP BY date6, msg.from_number, msg.to_number
            HAVING maxId IN
                   (
                     SELECT max(msg.id)
                     FROM tempMessages msg
                     WHERE msg.direction = "outgoing"
                     GROUP BY DATE_FORMAT(generated_time, dateFormat), msg.from_number, msg.to_number
                   ) AND maxId NOT IN (SELECT MIN(msgSub.id)
                                       FROM tempMessages msgSub
                                       GROUP BY msgSub.from_number, msgSub.to_number
                                       HAVING MIN(msgSub.id) = MAX(msgSub.id))
                     AND from_number IN ( select msg1.to_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='FAIL')
                     AND to_number IN ( select msg1.from_number
                                      from tempMessages msg1
                                      where msg1.direction = 'incoming' and msg1.type='FAIL')
          ) t
        GROUP BY date6
        ORDER BY date6
      ) AS respondedFailThreadsTable
        ON sentMessageTable.date = respondedFailThreadsTable.date6

DROP TABLE tempMessages;

END

Instead of creating a normal table i tried using temporary tables But i can't join a temp table multiple times in same query. So i tried using normal table which deletes after execution of the sp but it is also not helping to reduce the loading time. while executing the query in sp separately i get result in 10-20 seconds

Any solution to reduce loading time ?

标签： mysqlstored-procedurestemp-tables