Why is the candidate composite index not used?

Question

Consider the following table :

CREATE TABLE `entityusage_trackbyyear` (
  `track_id` binary(16) NOT NULL,
  `album_id` binary(16) DEFAULT NULL,
  `library_id` binary(16) DEFAULT NULL,
  `firstdateofinterval` date NOT NULL,
  `territory_id` binary(16) NOT NULL,
  `playbacks` int(10) unsigned DEFAULT 0,
  `userplaybacks` int(10) unsigned DEFAULT 0,
  `userdownloads` int(10) unsigned DEFAULT 0,
  `usersingledownloads` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`firstdateofinterval`,`track_id`,`territory_id`),
  KEY `album_id` (`firstdateofinterval`,`album_id`,`territory_id`),
  KEY `library_id` (`firstdateofinterval`,`library_id`,`territory_id`),
  KEY `territory_id` (`firstdateofinterval`,`territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the following query :

SELECT 
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

Why does MariaDB insist on using the PRIMARY KEY index, and not the library_id index which is a composite index over both firstdateofinterval + library_id - which are the columns given in the query criteria?

When i explicitly set USE INDEX (library_id) it skips using an index entirely.

I must be doing something wrong!

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: entityusage_trackbyyear
         type: range
possible_keys: PRIMARY,album_id,library_id,territory_id
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 1218204
       r_rows: 685956.00
     filtered: 100.00
   r_filtered: 0.13
        Extra: Using where; Using temporary; Using filesort

• The UUID_TO_BIN and BIN_TO_UUID are functions used to convert UUIDs to / from the "human readable" representation to a compact binary(16) data type.

Answer 1

You have to FORCE the index, the optimizer will only then use your wanted index, else you give it only a hint

SELECT 
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
    FORCE INDEX (territory_id)
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

Answer 2

• INDEX(library_id, firstdateofinterval) will help the query run faster. Note that the column tested by = should be first and the range should be last.
• Why not use the desired index? Because it won't get past the range. That is, all 4 of your indexes are equivalent to simply INDEX(firstdateofinterval) when you test for a range of dates.
• When you used the "index hint", did it actually run any faster?

Unrelated... UUIDs are inefficient when the table is bigger than RAM.