Search code examples
mysqlindexinginnodbdatabase-indexes

Index for query with WHERE clause and ORDER BY not used

I have a table created as follows

CREATE TABLE IF NOT EXISTS
`table`
  (
     id          VARCHAR(100) NOT NULL,
     seq_id      BIGINT UNSIGNED NOT NULL,
     in_use      BOOLEAN NOT NULL DEFAULT false,
     scheduled   BOOLEAN NOT NULL DEFAULT false,
     dialed      BOOLEAN NOT NULL DEFAULT false,
     priority    INT UNSIGNED NOT NULL DEFAULT 0,
     data_0      VARCHAR(100) NULL,
     data_1      VARCHAR(100) NULL,
     data_2      VARCHAR(40) NULL,
     data_3      VARCHAR(200) NULL,
     data_4      VARCHAR(10) NULL,
     data_5      DECIMAL(65, 20) NULL,
     data_6      DECIMAL(65, 20) NULL,
     PRIMARY KEY (`id`)
  )

Have a large query selecting rows according to a where clause and then sorting the results.

SELECT id
FROM
`table`
WHERE  ( dialed = false
         AND in_use = false
         AND scheduled = false )
ORDER  BY priority DESC,
          data_6 ASC,
          data_5 DESC,
          data_4 ASC,
          data_3 DESC,
          seq_id
LIMIT  100

I'm trying to find an optimal index for this query by running EXPLAIN on it. I've created a few different indexes;

  1. (dialed, in_use, scheduled, priority, data_6, data_5, data_4, data_3, seq_id)
  2. (priority, data_6, data_5, data_4, data_3, seq_id)
  3. (scheduled, in_use, dialed, priority, data_6)
  4. (scheduled, in_use)

The EXPLAIN query shows the following;

possible_keys: [index1],[index3],[index4]         
key: [index4]
key_len: 2
ref: const, const
rows: 448
filtered: 100.0
Extra: Using index condition; Using where; Using filesort

I'm curious why the indexes containing ORDER BY columns (index1 and index3) aren't used, and why it chooses the index that contains only a subset of the WHERE clause columns instead? I assumed that index1, with full column coverage for the query, would be ideal.

The index that is covering only the ORDER BY columns (index2) isn't showing up at all in the possible_keys. Is the order I've defined the index in here wrong?

Is it possible for a query to use one index for filtering, and then another for sorting the results?

As you can see, I'm running this test with 448 rows. This query could be run on far larger tables; up to a million. With larger tables, could the other indexes end up being more performant than index4?

Lastly, could an index with has many columns as index1 be less performant simply due to the number of columns?

Solution

  • 3 maybes:

    If this combination is selective "enough", then it is useful: INDEX(dialed, in_use, schedule). The order of those 3 does not matter.

    If you are using MySQL 8.0, then this might be useful (in the given order):

    INDEX(priority DESC,
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)

    Older versions ignore DESC, making them not use the INDEX

    Or even (again on 8.0):

    INDEX(dialed, in_use, schedule,   -- in any order
      priority DESC,    -- the rest, as specified
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)