Not the answer you need?
Register and ask your own question!

Slow query on Percona 5.6.14 (please help)

d3vnuld3vnul EntrantCurrent User Role Beginner
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:
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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.