I need to optimise this insane looking query (legacy code):
SELECT
E.eventId,
E.currency,
COALESCE(ROUND(UR.ratings, 2), 0) as ratings,
COALESCE(UR.ratingCount, 0) as ratingCount,
E.shopSpaceAvail,
E.floorPlanImage,
COALESCE(O.goingCount, 0) as goingCount,
(COALESCE(O.goingGroup, '')) as goingGroup,
E.userId,
E.name,
E.withoutTicket,
E.mainImage,
E.mainImageThumb,
E.privateEvent,
E.location,
E.locationLatitude,
E.locationLongitude,
E.country3Code,
E.country,
E.city,
E.description,
E.startDt as startDt_formatted,
E.endDt as endDt_formatted,
(
SELECT
COUNT(*)
FROM
eventIntresteds
WHERE
eventId = E.eventId
) as interestedCount,(
CASE WHEN "kaka" = "" THEN 0 ELSE (
SELECT
COUNT(*)
FROM
eventIntresteds as EI3
WHERE
EI3.eventId = E.eventId
AND EI3.userId IN (
48,
1872,
2039,
67132,
1076,
1880,
3504,
3641,
4575,
3080,
67129,
67130,
67134
)
) END
) as mutualInterestedCount,
COALESCE(
(
SELECT
MIN(adultPrice)
FROM
eventTickets as ET
WHERE
ET.deleted = '0'
AND ET.eventId = E.eventId
AND ET.eventTicketType = 'Normal'
),
0
) as minPrice,
(
CASE WHEN 'kaka' = '' THEN '2' WHEN (
(
SELECT
COUNT(*)
FROM
eventIntresteds EI1
WHERE
EI1.eventId = E.eventId
AND EI1.userId = 2162
) > 0
) THEN '1' ELSE '0' END
) as isInterested,
(
ROUND(
(
(
3959 * acos(
cos(radians(0)) * cos(radians(E.locationLatitude)) * cos(radians(E.locationLongitude) - radians(0)) + sin(radians(0)) * sin(radians(E.locationLatitude))
)
) * 1.67
),
4
)
) as distance,
(
CASE WHEN E.privateEvent = '0'
OR E.userId = 2162 THEN '1' WHEN 'kaka' = '' THEN '0' WHEN (
(
SELECT
COUNT(*)
FROM
userNotifications UN
WHERE
UN.eventId = E.eventId
AND UN.userId = 2162
AND UN.notificationType = 'eventInvite'
) = 0
) THEN '0' ELSE '1' END
) as isprivateEvent,
(
CASE WHEN (
E.privateEvent = '0'
or E.userId = 2162
) THEN 1 ELSE (
SELECT
COUNT(*)
FROM
invites AS I
WHERE
I.eventId = E.eventId
AND I.inviteType = 'Event'
AND I.deleted = '0'
AND I.userId = 2162
) END
) as privateHavingCheck,
(
SELECT
COUNT(Distinct O1.shopId)
FROM
orders O1
JOIN shops S1 ON (S1.shopId = O1.shopId)
WHERE
O1.eventId = E.eventId
AND S1.deleted = '0'
AND S1.blocked = '0'
AND O1.orderStatus = 'Success'
AND O1.paymentStatus = 'Success'
AND O1.orderType = 'shopBooth'
AND O1.refundId = ''
) as shopCount
FROM
events AS E
JOIN users U ON (
U.userId = E.userId
AND U.blocked = '0'
)
LEFT JOIN (
SELECT
COUNT(*) as ratingCount,
AVG(ratings) as ratings,
eventId
FROM
userRatings
WHERE
userRatings.blocked = '0'
AND userRatings.deleted = '0'
GROUP BY
eventId
) as UR ON (UR.eventId = E.eventId)
LEFT JOIN (
SELECT
COUNT(*) as goingCount,
GROUP_CONCAT(userId) as goingGroup,
eventId
FROM
orders AS O
WHERE
O.orderStatus = 'Success'
AND O.paymentStatus = 'Success'
AND orderType = 'EventTicket'
GROUP BY
eventId
) as O ON (O.eventId = E.eventId)
WHERE
E.blocked = '0'
AND E.deleted = '0'
AND E.approved = '1'
AND E.privateEvent = '0'
AND E.endDt >= now()
Having
eventId != 0
AND isprivateEvent = '1'
AND distance <= 1000000
ORDER BY
minPrice DESC,
minPrice DESC
LIMIT
0, 10;
I tried to create indexes for this, but nothing seems to help. The query is super slow. The wort part seems to be this:
eventId FROM orders AS O WHERE O.orderStatus='Success' AND O.paymentStatus='Success' AND orderType='EventTicket' GROUP BY eventId) as O ON (O.eventId=E.eventId)
This scans 10K+ rows according to explain(). I tried a compound index on (orderStatus,paymentStatus,orderType) but it doesn't help.
Any suggestions about how to quickly optimise this query? I know it should be refactored, but there is no time for that. I also know it's bad, so I don't want it to be super fast. But at this point any speed up would be appreciated.
This is MySQL 5.7.
EDIT:
Here is the explain:
+----+--------------------+-----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+----------------------------------------+-------+----------+-----------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+----------------------------------------+-------+----------+-----------------------------------------------------------------------------------+
| 1 | PRIMARY | E | NULL | ref | userId,combo,events_idx_blocke_delete_approv_privat_enddt | events_idx_blocke_delete_approv_privat_enddt | 4 | const,const,const,const | 359 | 33.33 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | PRIMARY | U | NULL | eq_ref | PRIMARY,blocked,users_idx_blocked_userid | PRIMARY | 8 | db.E.userId | 1 | 50.00 | Using where |
| 1 | PRIMARY | <derived9> | NULL | ref | <auto_key0> | <auto_key0> | 8 | db.E.eventId | 2 | 100.00 | NULL |
| 1 | PRIMARY | <derived10> | NULL | ref | <auto_key0> | <auto_key0> | 8 | db.E.eventId | 18 | 100.00 | NULL |
| 10 | DERIVED | O | NULL | index_merge | paymentStatus,orderStatus,orderType,eventIdAndDate,eventId,complexIdx3,complexIdx4,complexIdx5,sds | paymentStatus,orderStatus,orderType | 22,22,22 | NULL | 10019 | 100.00 | Using intersect(paymentStatus,orderStatus,orderType); Using where; Using filesort |
| 9 | DERIVED | userRatings | NULL | index | complexIdx1,eventId | eventId | 8 | NULL | 43 | 25.00 | Using where |
| 8 | DEPENDENT SUBQUERY | O1 | NULL | range | shopId,paymentStatus,orderStatus,orderType,refundId,eventIdAndDate,eventId,complexIdx3,complexIdx4,complexIdx5,sds | complexIdx3 | 44 | NULL | 78 | 2.50 | Using index condition; Using where |
| 8 | DEPENDENT SUBQUERY | S1 | NULL | eq_ref | PRIMARY,shopId,blocked,deleted,shops_idx_deleted_blocked_shopid | PRIMARY | 8 | db.O1.shopId | 1 | 80.93 | Using where |
| 7 | DEPENDENT SUBQUERY | I | NULL | ref | userId,eventId,invites_idx_invitet_deleted_userid_eventid | invites_idx_invitet_deleted_userid_eventid | 39 | const,const,const,db.E.eventId | 1 | 100.00 | Using index |
| 6 | DEPENDENT SUBQUERY | UN | NULL | ref | userId,evId,notType,combo,usernotifications_idx_userid_notificati_eventid | usernotifications_idx_userid_notificati_eventid | 38 | const,const,db.E.eventId | 368 | 100.00 | Using index |
| 5 | DEPENDENT SUBQUERY | EI1 | NULL | eq_ref | eventId_2,eventId,userId,eventintresteds_idx_userid_eventid | eventId_2 | 16 |db.E.eventId,const | 1 | 100.00 | Using where; Using index |
| 4 | DEPENDENT SUBQUERY | ET | NULL | ref | eventId,type,deleted,delEvId,eventtickets_idx_deleted_eventti_eventid_adultpr | eventtickets_idx_deleted_eventti_eventid_adultpr | 91 | const,const,db.E.eventId | 24 | 100.00 | Using index |
| 3 | DEPENDENT SUBQUERY | EI3 | NULL | ref | eventId_2,eventId,userId,eventintresteds_idx_userid_eventid | eventId_2 | 8 |db.E.eventId | 108 | 0.84 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | eventIntresteds | NULL | ref | eventId_2,eventId | eventId | 8 |db.E.eventId | 107 | 100.00 | Using where; Using index |
+----+--------------------+-----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+----------+----------------------------------------+-------+----------+-----------------------------------------------------------------------------------+
EDIT2:
If anyone could suggest one or more indexes to speed this up, that would be appreciated. This query is generated by ORM (Sequilize), so there is no easy way to just change it manually.
(Please provide SHOW CREATE TABLE for each table.)
In general JOIN ( SELECT ... ) is less efficient than other techniques.
Tentatie indexes:
E: INDEX(blocked, deleted, approved, privateEvent,
endDt) -- last in this index
orders: INDEX(paymentStatus, orderStatus, orderType, -- these first
refundId, eventId, shopId)
invites: INDEX(userId, deleted, inviteType, eventId)
UN: INDEX(notificationType, eventId, userId)
eventIntresteds: INDEX(eventId, userId) -- in this order
Simplification and optimization:
( SELECT COUNT(*)
FROM eventIntresteds EI1
WHERE EI1.eventId = E.eventId
AND EI1.userId = 2162 ) > 0 ) THEN '1' ELSE '0' END
-->
EXISTS ( SELECT 1
FROM eventIntresteds EI1
WHERE EI1.eventId = E.eventId
AND EI1.userId = 2162 )
There are several CASE clauses, some may work better by using EXISTS.