CODEWITHSUNDEEP
mysqlsql-order-bymariadb
Rudie
Rudie

Reputation: 53831

Very slow SQL ORDER BY, and EXPLAIN doesn't explain

The query:

SELECT
    m.*,
    mic.*
FROM
    members m,
    members_in_class_activities mic
WHERE
    m.id = mic.member_id AND
    mic.not_cancelled = '1' AND
    mic.class_activity_id = '32' AND
    mic.date = '2016-02-14' AND
    mic.time = '11:00:00'
ORDER BY
    mic.reservation_order

Table members has ~ 100k records, and table members_in_class_activities has about 300k. The result set is just 2 records. (Not 2k, just 2.)

All relevant columns are indexed (id and reservation_order are primary): member_id, not_cancelled, class_activity_id, date, time.

There is also a UNIQUE key for class_activity_id + member_id + date + time + not_cancelled. not_cancelled is NULL or '1'.

All other queries are very fast (1-5 ms), but this one is crazy slow: 600-1000 ms.

What doesn't help:

What does help, immensely:

I have only 2 questions:

  1. What????
  2. How do I still ORDER BY x, but make it fast too? Do I really need a separate column?

I'm running 10.1.9-MariaDB on my dev machine, but it's slow on production's MySQL 5.5.27-log too.

Upvotes: 2

Views: 172

Answers (2)

Rick James
Rick James

Reputation: 142296

class_activity_id + member_id + date + time + not_cancelled -- not optimal.

Start with the '=' fields in the WHERE in any order, then add on the ORDER BY field:

INDEX(class_activity_id, date, time, not_cancelled,
      reservation_order)

Since you seem to need the UNIQUE constraint, then it would be almost as good to shuffle your index by putting member_id at the end (where it will be 'out of the way', but not used):

UNIQUE(class_activity_id, date, time, not_cancelled, member_id)

In general, it is bad to split a date and time apart; suggest a DATETIME column for the pair.

Cookbook on creating indexes.

Upvotes: 0

Mr. Engineer
Mr. Engineer

Reputation: 3515

Dont use order by on your main query. Try this : 

SELECT * FROM (   
  ... your query
) ORDER BY mic.reservation_order

As you mentioned that members_in_class_activities has about 300k records, so your order by will apply on all 300k records that definitely slow down your query.

Upvotes: 3

Related Questions