MySQL Forums
Forum List  »  InnoDB

Re: slow query in Inndb
Posted by: Rick James
Date: May 30, 2012 08:20PM

(This question is probably not InnoDB-specific.)

Ordinarily, I would insist on seeing the SHOW CREATE TABLE before guessing what the issues are.

More readable:
SELECT  c.id AS id, lastname AS customername,
        status, address, zip, city, countryid,
        countries.name AS country, email,
        ten, ssn, c.info AS info, message,
        c.divisionid, c.paytime AS paytime,
        COALESCE(b.value, 0) AS balance, COALESCE(t.value, 0) AS tariffvalue,
        s.account, s.warncount,
        s.online,
        (CASE WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS nodeac,
        (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0 THEN 2 ELSE 0 END) AS nodewarn
    FROM  customersview c
    LEFT JOIN  countries ON (c.countryid = countries.id)
    LEFT JOIN  
      ( SELECT  SUM(value) AS value, customerid
            FROM  cash
            GROUP BY  customerid
      ) b ON (b.customerid = c.id)
    LEFT JOIN  
      ( SELECT  customerid, SUM((1)) AS value
            FROM  assignments
            LEFT JOIN  tariffs t ON (t.id = tariffid)
            LEFT JOIN  liabilities l ON (l.id = liabilityid AND  period != 0)
            WHERE  (datefrom <= 1337932817  OR  datefrom = 0)
              AND  (dateto   >  1337932817  OR  dateto = 0)
            GROUP BY  customerid
      ) t ON (t.customerid = c.id)
    LEFT JOIN  
      ( SELECT  ownerid, SUM(access) AS acsum, COUNT(access) AS account,
                SUM(warning) AS warnsum, COUNT(warning) AS warncount,
                (CASE WHEN MAX(lastonline) > 1337932817 - 0 THEN 1 ELSE 0 END) AS online
            FROM  nodes
            WHERE  ownerid > 0
            GROUP BY  ownerid
      ) s ON (s.ownerid = c.id);

The first problem I see is more than one "JOIN ( SELECT ...)". Each such subquery will generate a temporary table. They will have no indexes, nor any way to add an index, so they will have to be fully scanned, repeatedly. This is the cause of the high CPU.

Normally, I would suggest turning each subquery into a JOIN. But that is probably counter-productive since the subqueries include a GROUP BY.

(Rick's RoTs mentions some of these issues: http://mysql.rjweb.org/doc.php/ricksrots )

There is a workaround. Do something like this for each of the three subqueries (using the first as an example):
CREATE TEMPORARY TABLE b (
        PRIMARY KEY (customerid)
    ) ENGINE=MyISAM
        SELECT  SUM(value) AS value, customerid
            FROM  cash
            GROUP BY  customerid;
Then change:
    LEFT JOIN  
      ( SELECT  SUM(value) AS value, customerid
            FROM  cash
            GROUP BY  customerid
      ) b ON (b.customerid = c.id)
-->
    LEFT JOIN b ON (b.customerid = c.id)

Also, "LEFT" is used when the 'right' table might be missing rows, and you want NULLs for anything missing from it. So, remove "LEFT" if you don't need that feature.

> 6 6 DEPENDENT SUBQUERY e index userid 8 276 Using where; Using index
Am I blind, or is there no table "e" in your query?

> (datefrom <= 1337932817 OR datefrom = 0)
The OR makes it difficult to optimize.

Options: ReplyQuote


Subject
Views
Written By
Posted
2722
May 29, 2012 09:32AM
Re: slow query in Inndb
1096
May 30, 2012 08:20PM
1111
May 31, 2012 01:23AM
875
May 31, 2012 09:40PM
965
June 01, 2012 10:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.