Why is the candidate composite index not used? 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 &gt;= '2018-01-01'
        AND firstdateofinterval &lt;= '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 &quot;human readable&quot; representation to a compact binary(16) data type.
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 &gt;= '2018-01-01'
        AND firstdateofinterval &lt;= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e