0

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.
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
| improve this answer | |
  • Thanks @nbk. This is a very obvious index to use. I was under the impression that mariadb will use the most appropriate index, and it puzzles me that it does not even pick that index when given a hint? – sbrattla Jun 22 at 10:28
  • the optimizer has its programming and it works in most cases, with only 1 or two indexes, but with a bunch of them, its algorithms calculate, but as you noticed, the alghorithm needs much improvement – nbk Jun 22 at 10:41
  • @nbk - Why use the territory_id index? It is not even involved in the WHERE or ORDER BY?? – Rick James Jun 24 at 6:07
  • no particular reason, it was teh first that appeared in view, you have to test them and discard the not used indexes. – nbk Jun 24 at 7:28
1
  • 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.

| improve this answer | |
  • Thanks @Rick James. I will try to redo the index. I know UUIDs are inefficient, but that's just how it is. I can't fix that. However, that is why I've been trying to compact the tables a little by storing the UUIDs as binary. – sbrattla Jun 24 at 6:22
  • @sbrattla - Yes, storing as Binary helps. For a tiny bonus, consider changing other columns from a 4-byte INT to some smaller numeric type. – Rick James Jun 24 at 6:25
  • Thanks @Rick James, yes, there's actually no reason to use INT(10) . The max value in any of these fields is in the thousands, so a SMALLINT should do. I assume the "to be" column data type (SMALLINT) will not then prevent me from doing a SUM over the field? That is, the data type of the SUM'ed fields will not be limited to the size of SMALLINT? – sbrattla Jun 24 at 8:33
  • 1
    @sbrattla - Valid question; but there is no problem. SUM() and + are performed in BIGINT (or DOUBLE), then stored into the size of the target column. (- and UNSIGNED sometimes cause some hiccups.) – Rick James Jun 24 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.