MariaDB upgrade 10.3 to 10.9, indexes, eq_range_index_dive_limit and EXPLAIN

Question

I'm currently in the process of upgrading and MariadDB server from 10.3 (10.3.38-MariaDB-0ubuntu0.20.04.1) to 10.9 (10.9.3-MariaDB-1:10.9.3+maria~ubu2004-log)

I've been running the 10.9 version locally for some time in prep for putting it up in the production environment. As I've been working to optimize some larger queries, I've noticed locally that indexes weren't being used in some cases while in production, they were.

As I understand it, it's the changes between these version numbers and a few settings and the engine trying to be "smarter" about the cost of using optimization vs the cost of just proceeding forward with a table scan sans index use. Lot's of googling has yielded posts about:

eq_range_index_dive_limit, use_stat_tables, sort_buffer_size

I may be a bit old-fashioned but when I run an EXPLAIN and I see possible keys, but not used, that indicates some kind of problem. Assuming that, no variation of ANALYZE TABLE has worked to cause an index to start being used. I observed the same behavior on a test production machine after the upgrade as index usage there was duplicated as to what I was seeing locally.

One post that was concerning to me was the scenario where the actual result of the query was not well "measured" by the engine, and forcing an index was the only option. This is what I'm concerned about - putting 10.9 up into my production environment under production load (which is difficult for me to reliable simulate) and seeing slowdowns.

What am I do to do about this:

1. Trust the changes between the version numbers, and cross my fingers that forgoing index usage will perform better, or
2. Run all my queries through explain and start forcing indexes all over the place (which I did before I realized what was going on), because
3. I'm not even really sure how to gauge what's better, letting the engine decide not to use an index, or me forcing the index.

I'd like some feedback on how some of you have dealt with this, or even suggestions about tuning to get more index usage? Will I really experience slowdowns if using the index is not as good as table scanning?

FWIW, I've been up and down the setting values for those three server options above, and nothing changed the index usage for that simple SELECT statement...

Thanks for your input, context below.

MariaDB [pweb]> EXPLAIN extended select * from `accounting_transactions` where `accounting_transactions`.`cr_account` = 'f7d78ef5-ca59-44d1-9d67-70a83960f473' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounting_transactions
         type: ALL
possible_keys: accounting_transactions_cr_account_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 532030
     filtered: 35.63
        Extra: Using where
1 row in set, 1 warning (0.002 sec)

MariaDB [pweb]> EXPLAIN extended select * from `accounting_transactions` force index (accounting_transactions_cr_account_index ) where `accounting_transactions`.`cr_account` = 'f7d78ef5-ca59-44d1-9d67-70a83960f473'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounting_transactions
         type: ref
possible_keys: accounting_transactions_cr_account_index
          key: accounting_transactions_cr_account_index
      key_len: 144
          ref: const
         rows: 189558
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.001 sec)

MariaDB [pweb]> SELECT VERSION();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 10.9.3-MariaDB-1:10.9.3+maria~ubu2004-log |
+-------------------------------------------+
1 row in set (0.001 sec)

MariaDB [pweb]> show create table accounting_transactions \G
*************************** 1. row ***************************
       Table: accounting_transactions
Create Table: CREATE TABLE `accounting_transactions` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `event_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `dr_account` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cr_account` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accounting_transactions_event_id_index` (`event_id`),
  KEY `accounting_transactions_dr_account_index` (`dr_account`),
  KEY `accounting_transactions_cr_account_index` (`cr_account`),
  CONSTRAINT `accounting_transactions_cr_account_foreign` FOREIGN KEY (`cr_account`) REFERENCES `accounting_accounts` (`id`),
  CONSTRAINT `accounting_transactions_dr_account_foreign` FOREIGN KEY (`dr_account`) REFERENCES `accounting_accounts` (`id`),
  CONSTRAINT `accounting_transactions_event_id_foreign` FOREIGN KEY (`event_id`) REFERENCES `accounting_events` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.002 sec)

MariaDB [pweb]> SHOW variables LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.002 sec)

MariaDB [pweb]> SHOW variables where Variable_name in ('eq_range_index_dive_limit', 'use_stat_tables', 'sort_buffer_size')\G
*************************** 1. row ***************************
Variable_name: eq_range_index_dive_limit
        Value: 100000000
*************************** 2. row ***************************
Variable_name: sort_buffer_size
        Value: 209715200
*************************** 3. row ***************************
Variable_name: use_stat_tables
        Value: NEVER
3 rows in set (0.002 sec)

In some situations based on actual query time - I noticed a faster return without index usage, which I have to say is pretty surprising. Does this support option 1?

Answer 1

91785/538368 -- 17% of the table is used.

It is usually faster to do a table scan when that much of the table is being fetched. When an index is used, it needs to bounce back and forth between the index's BTree and the data's BTree. This bouncing may take longer than simply doing a linear scan through the data, tossing 83% of the rows.

Alas, the Optimizer has non adequate statistics to say which way is faster.

The size of the result set in this case would probably be too big to fit in the Query Cache. So, even if it were turned on, trying to use the QC might just lead to extra overhead.

One thing you can do is to shrink the CHAR(36) strings. Either use the UUID datatype or at least pack them down to BINARY(16). My UUIDs blog talks about such even before the datatype existed.

What the heck is the client going to do with 91785 rows? Another tip: If the client is not using all the columns, spell out just the necessary ones instead of using SELECT *.