Straightforward inner join is slow despite using indices and ref/eq_ref types

Question

I have a couple moderately-sized MySQL 8.0.30 InnoDB tables:

• models - 1,000,000 rows
• activations - 10,000,000 rows

A fairly simple INNER JOIN takes about 500ms, which seems like a lot (is it?). This INNER JOIN is run every 1-2 seconds, as I need the value more often than it makes sense to cache.

For comparison, I also have several queries that run 25 times per second, with a mean time of 0.3ms - 2ms per query. My database has started taking longer to respond after an influx of users and surprisingly, the biggest issue seems to be the INNER JOIN query below.

SELECT COUNT(*) FROM activations
INNER JOIN models ON activations.model_id = models.id
WHERE activations.ref_id = @ref_id
AND activations.ignore = 0
AND models.is_test = false

The count returned is usually around 50,000. @ref_id can be any valid refs.id, but on a given day, 90% or more of the queries will use the same value for that day.

Any thoughts as to why this is slow? Why is the "filtered" number so high for the join?

I've tried using FORCE INDEX (is_test) but that's worse.

Analyze

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	activations	NULL	ref	PRIMARY,ref_id,by_success_count	by_success_count	5	const,const	84528	100.00	Using index
1	SIMPLE	models	NULL	eq_ref	PRIMARY,is_test	PRIMARY	4	activations.model_id	1	50.00	Using where

Explain Analyze

-> Aggregate: count(0)  (cost=41148.69 rows=1) (actual time=488.845..488.846 rows=1 loops=1)
    -> Nested loop inner join  (cost=37044.59 rows=41041) (actual time=0.073..479.428 rows=48862 loops=1)
        -> Covering index lookup on activations using by_success_count (ref_id=498, ignore=0)  (cost=8315.89 rows=82082) (actual time=0.052..82.886 rows=48918 loops=1)
        -> Filter: (models.is_test = false)  (cost=0.25 rows=0.5) (actual time=0.008..0.008 rows=1 loops=48918)
            -> Single-row index lookup on models using PRIMARY (id=activations.model_id)  (cost=0.25 rows=1) (actual time=0.007..0.007 rows=1 loops=48918)

Schema:

CREATE TABLE `activations` (
  `model_id` int NOT NULL,
  `ref_id` int NOT NULL,
  `success_count` smallint UNSIGNED NOT NULL,
  `score` smallint UNSIGNED NOT NULL,
  `ms` bigint DEFAULT NULL,
  `ignore` tinyint(1) NOT NULL DEFAULT '0',
  `ref_version` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `activations`
  ADD PRIMARY KEY (`model_id`,`ref_id`),
  ADD KEY `ref_id` (`ref_id`),
  ADD KEY `by_success_count` (`ref_id`,`ignore`,`success_count`,`model_id`,`ms`) USING BTREE;

ALTER TABLE `activations`
  ADD CONSTRAINT `activations_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `models` (`id`);
COMMIT;


CREATE TABLE `models` (
  `id` int NOT NULL,
  `name` varchar(14) DEFAULT NULL,
  `uuid` varchar(36) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `version` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
  `is_test` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `models`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uuid` (`uuid`),
  ADD UNIQUE KEY `name` (`name`),
  ADD KEY `is_test` (`id`, `is_test`) USING BTREE;

Some stats:

innodb_buffer_pool_size in MB is 2944.

activations.ref_id has about 500 distinct values, with a new one per day. There are 20k - 50k rows for each of these values.

ANALYZE TABLE yields Msg_text of OK for both tables.

Some more info from mysql_tuner.pl:

[!!] InnoDB buffer pool / data size: 2.9G / 7.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (123.641304347826%): 1.8G * 2 / 2.9G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 8 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.63% (137988608800 hits / 138507499187 total)
[!!] InnoDB Write Log efficiency: 65.29% (747214582 hits / 1144453858 total)

--

After implementing the indices from this response, the time to run is about the same when I force either of both of those indices (both queries are running in about 300-350ms as the site is a bit less active right now):

-> Aggregate: count(0)  (cost=138515.70 rows=1) (actual time=340.244..340.245 rows=1 loops=1)
    -> Nested loop inner join  (cost=127877.70 rows=106380) (actual time=0.068..334.396 rows=55254 loops=1)
        -> Covering index lookup on activations using ref_ignore_model (ref_id=498, is_ignore_ref=0)  (cost=10859.70 rows=106380) (actual time=0.055..26.179 rows=55313 loops=1)
        -> Single-row covering index lookup on models using is_test_id (is_test=false, id=activations.model_id)  (cost=1.00 rows=1) (actual time=0.005..0.005 rows=1 loops=55313)

--

One more note: Removing all of the WHERE clauses except the one on refs.id has very little effect on the overall time. Most models have is_test = 0 and most activations have ignore = 0. Replacing the INNER JOIN with WHERE model_id NOT IN (SELECT...) is treated as an antijoin and has the same perf.

If I remove the INNER JOIN (since I've removed the models.is_test WHERE clause), however, that drops us down to 28ms. So maybe I'll look into better ways to pull in that models.is_test info.

Answer 1

The problem is that for each elibigle activation, MySql has to check if the corresponding model is a test, before it can be counted. There are 50.000 activations on average, so 50.000 checks. An index on (id, is_test) doesn't reduce the number of checks, and even an index on is_test is not useful for your query, since most models have is_test = 0 and the index is not very selective.

But you can turn this to your advantage: Drop the join, create an index on models(is_test, id) (note the reverse order) and try a query like this:

SELECT COUNT(*) FROM activations
WHERE activations.ref_id = @ref_id
AND activations.ignore = 0
AND activations.model_id NOT IN (SELECT models.id from models where is_test = true)

This way you use the condition of is_test = true which is way more selective and should make use of the index and return just a few model id's. The rest of the query should just be a fast index scan of activations, checking that the model_id is not one of those to be excluded.

EDIT: If the NOT IN is handled as an anti-join, try counting all and then subtracting the tests:

SELECT (
    SELECT COUNT(*) FROM activations
    WHERE activations.ref_id = @ref_id
    AND activations.ignore = 0
    ) - (
    SELECT COUNT(*) FROM activations
    JOIN models ON activations.model_id = models.id
    WHERE activations.ref_id = @ref_id
    AND activations.ignore = 0
    AND models.is_test = true
    ) as difference;

Answer 2

Depending on the distribution of the data, these may help with performance.

For models:

INDEX(is_test, id)

Notice that the Optimizer shunned (id, is_test), which it probably decided was no better than the PRIMARY KEY(id).

For activations:

INDEX(ref_id, ignore, model_id)

Please provide EXPLAIN ANALYZE SELECT ...

As for "filtered" -- I don't put much trust it that.

Another thing that may help: ANALYZE TABLE.