Query index with WHERE clause and ORDER BY not used

Question

I created a table as shown below

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 that selects rows based on where clause and then sorts 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 the best index for this query by running EXPLAIN. I 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. (booked, in use)

EXPLAIN query displays 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 it doesn't use an index that contains the ORDER BY columns (index1 and index3), and why it selects an index that only contains a subset of the WHERE clause columns? I think index 1 has full column coverage of the query and is ideal.

The index (index2) covering only the ORDER BY column is not shown in possible_keys at all. Am I defining the indexes in the wrong order here?

Is it possible for a query to use one index to filter and then use another index to sort the results?

As you can see, I'm running 448 lines of tests. The query can be run on larger tables; up to one million. For larger tables, will other indexes end up being more performant than index 4?

Finally, will an index like index 1 with many columns degrade performance just because of the number of columns?

Answer 1

3 possibilities:

This combination is useful if it is selectively "enough": INDEX(dialed, in_use, Schedule). The order of these 3 is not important.

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

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

Older versions ignore DESC, making them unusable INDEX

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)