Optimize crazy MySQL query

Question

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.

Answer 1

Note I am assuming from your question that there is no opportunity to change the query syntax, since you said it was generated by an ORM, and you want only "quick fixes" in the form of indexes.

All the subqueries in the select-list have Using index in the notes, so they are using covering indexes already. I doubt there's anything you can do to optimize these further. Correlated subqueries are just nasty to optimize, because they must execute once for every row of the result, before conditions in the HAVING clause.

Table events as E is well-optimized, if I can guess that the index events_idx_blocke_delete_approv_privat_enddt is on the four columns referenced in the WHERE clause.

Table users AS U is accessed by PRIMARY key, so there's probably no way to optimize that further with an index.

The derived table UR is in need of optimizing. I'd add this index:

ALTER TABLE userRatings ADD INDEX (blocked, deleted, eventId, ratings);

(I'll leave the index name to you, since you know what naming convention you want to use.)

The derived table O is doing an index-intersection of three separate indexes. Usually a single compound index is better than relying in index-merge. I'd add this index:

ALTER TABLE orders ADD INDEX (orderStatus, paymentStatus, orderType, eventId, userId);

I know you said you tried a compound index on the first three columns, but adding the other two columns could help it as a covering index.

Unfortunately the ORDER BY is sorting by results of correlated subqueries, so there's no way to optimize away the Using filesort.

This is simply a monster query, trying to do several things at once (e.g. all the correlated subqueries). Anything you do as a quick fix isn't going to help much. You really need to refactor this to get a major improvement.

Answer 2

(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.