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.


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)


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

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`);

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 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.

  • If 0.5 seconds is "slow", what would you consider "acceptable"? What does "show engine innodb status" tell you after you run your query a few times?
    – mustaccio
    Feb 22 at 1:47
  • @mustaccio I guess that's part of my question -- is that NOT slow? It's the most expensive query I have by orders of magnitude when counting total time despite being called MUCH less than other queries. My site has a lot of concurrent users. Will show engine innodb status still be helpful? The main thing you might want is: (averages calculated over the last 11 seconds) Pending flushes (fsync) log: 0; buffer pool: 18446744073709551537 518239186 OS file reads, 461232488 OS file writes, 322089863 OS fsyncs 76.39 reads/s, 16384 avg bytes/read, 265.12 writes/s, 181.35 fsyncs/s
    – mgiuffrida
    Feb 22 at 2:02
  • Please add details to your question by editing it, and don't guess what anybody "might want"; it's better to provide the full output. If you have a particular performance problem with the application, please state so; if you're looking at the "most expensive query" just because you think it's expensive, I think you will be better off investing your energy elsewhere.
    – mustaccio
    Feb 22 at 2:13
  • What is the value of innodb_buffer_pool_size?
    – Rick James
    Feb 22 at 2:55
  • What percentage of the 10M rows is @ref_id ?
    – Rick James
    Feb 22 at 2:56

2 Answers 2


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 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;
  • Thanks for the detailed explanation. Since the optimizer insists on doing an anti-join, the version that subtracts the count is best. Looking at around a 3x-4x speedup based on preliminary benchmarking! (Btw, I had already tried the index you suggested. A pretty substantially faster index, which the optimizer chooses, is a single-column index on is_test alone -- I think, either way, the index lookup is keyed on the id? So no point in including it in the index I guess. :-)
    – mgiuffrida
    Feb 23 at 22:40

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)


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

Another thing that may help: ANALYZE TABLE.

  • Thanks! I've added some info to the end of my question. Your suggested models index isn't used unless I force it, in which case the result is slightly slower. The optimizer does pick your activations index, but the time (and EXPLAIN ANALYZE output) is about the same.
    – mgiuffrida
    Feb 22 at 3:48
  • Actually the optimizer has stopped using the new index. Time is still about the same.
    – mgiuffrida
    Feb 22 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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