Announcement

Announcement Module
Collapse
No announcement yet.

Slow query on Percona 5.6.14 (please help)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow query on Percona 5.6.14 (please help)

    Hello to everyone,

    I'm trying to optimize a specific query to get fastest time possible. First thing that came to mind was to try Percona 5.6 on a different/better hardware:

    Hardware(both have same raid controller/cache/battery):
    MySQL SERVER: 4x300GB 10K SAS - RAID10 ( 8 cpu cores )
    Percona SERVER: 2x100GB Kingston SSD E100 - RAID1 ( 24 cpu cores )

    On MySQL 5.5.33 this query executes in average 15sec.
    On Percona 5.6.14 this query executes in average 10sec.

    Query:
    Code:
    SELECT
      a.*,
      d.cheap,
      d.name AS delivery_name,
      cu.name AS cuisine,
      IFNULL(a.sec_cuis, d.sec_cuis) AS sec_cuis,
      d.ticket,
      IF(
        TIMESTAMPDIFF(SECOND, a.online, NOW()) <= 120,
        1,
        0
      ) AS online,
      MAX(
        IF(
          (
            ts.time_from <= CURTIME()
            OR (
              ts.time_to >= CURTIME()
              AND ts.time_to < ts.time_from
            )
          )
          AND (
            ts.time_to >= CURTIME()
            OR (
              ts.time_to < ts.time_from
              AND ts.time_from <= CURTIME()
            )
          )
          OR (
            ts.time_to = ts.time_from
            AND ts.time_to IS NOT NULL
          ),
          1,
          0
        )
      ) AS OPEN,
      COUNT(DISTINCT re.id) AS reviews_comm
    FROM
      cats c,
      cuisines2cats cc,
      products p,
      specs s,
      cuisines cu,
      accounts a
      LEFT JOIN reviews re
        ON re.account_id = a.id
        AND re.approved = '1'
        AND re.active = '1',
      deliveries d,
      time_schedule ts
    WHERE ts.account_id = a.id
      AND ts.day = 'tue'
      AND a.active = '1'
      AND (
        a.delivery_type = 1
        OR a.delivery_type = '2'
      )
      AND d.id = a.delivery_id
      AND (
        d.def_cuis = cu.id
        OR a.def_cuis = cu.id
      )
      AND d.active = '1'
      AND cc.cat_id = c.id
      AND cc.cuisine_id = '3'
      AND a.delivery_id = c.delivery_id
      AND p.cat_id = c.id
      AND s.product_id = p.id
      AND s.account_id = a.id
    GROUP BY a.id
    ORDER BY RAND() ;
    Attached configs/create statements/explains.
    Really appreciate any help input.
    Last edited by d3vnul; 11-06-2013, 04:46 PM.
Working...
X