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.
percona_cnf.txt (2.15 KB)
mysql55_cnf.txt (1.54 KB)
explains.txt (4.83 KB)
create-statements.txt (12.2 KB)