Mysql – Slow query with JOIN

join;MySQLoptimizationperformancequery-performancestored-procedures

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN:

JOIN tariffs t ON 
(LEFT(`cdrs`.`cnumber`,7) = t.numberrange 
OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

Without the above JOIN the query runs at 1.5 sec.
Any way how to improve the performance? Full stored procedure below:

CREATE DEFINER=`xxx`@`%` PROCEDURE `GetHourStats`(IN _ID INT, IN _YEAR INT, IN _MONTH INT, IN _DAY INT)
BEGIN

set @_START = UNIX_TIMESTAMP(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1)); 
set @_END = UNIX_TIMESTAMP(date_add(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1), interval 1 day));

SELECT h.idhour, h.`hour` as 'hour', innumber, count(*) as `count`, sum(talktime) as `duration` FROM (
     SELECT 
        `cdrs`.`dcustomer` AS `dcustomer`,
        (CASE
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "01" OR LEFT(`cdrs`.`cnumber`, 2) = "02") THEN '01-02'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "03") THEN '03'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "05") THEN '05'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "06") THEN '06'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "07") THEN '07'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "080") THEN '080'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "084") THEN '084'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "087") THEN '087'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "09") THEN '09'
        END) AS 'innumber',
        FROM_UNIXTIME(`cdrs`.`start`) AS `start`,
         (`cdrs`.`end` - `cdrs`.`start`) AS `duration`,
         `cdrs`.`cnumber` AS `calling`,
         `cdrs`.`talktime` AS `talktime`
    FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)
    WHERE `cdrs`.`start` >= @_START and `cdrs`.`start` < @_END
    AND `cdrs`.`stype` = _LATIN1'external'
    AND `cdrs`.`talktime` >= 5 
    AND `cdrs`.`status` = 'answer'
    AND CHAR_LENGTH(`cdrs`.`cnumber`) = 11
    GROUP BY callid
   ) cdr 

   JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN 
        (SELECT id FROM customers WHERE parent = _ID))

   GROUP BY h.idhour, cdr.innumber
   ORDER BY h.idhour;

END

Q: How can I make the above stored procedure run faster?

Best Answer

I agree with the comment about table definitions, but in a few moments you will see this does not depend on the tables.

You use SELECT from derived table cdr for JOIN and for WHERE

JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN 
        (SELECT id FROM customers WHERE parent = _ID))

Derived tables do not have indexes and so these operations always will be full-scan. It may take 6 seconds. That is not too much.

The other JOIN uses derived columns:

FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

This is not the best idea.

Again, group by does not use indexed data:

GROUP BY h.idhour, cdr.innumber

cdr - does not have indexes.

Possibly faster will be to store intermediate data in a table with indexes.

Related Solutions

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.

Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.

According to the Book

kdsjx

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.

It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.

I hope this answers your question.

UPDATE 2014-01-31 11:26 EST

In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:

CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.

Best Answer

Related Solutions

Mysql – Slow performance of MySQL Join Query

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

UPDATE 2014-01-31 11:26 EST

Related Question