Why MariaDB doesn't use newly added index?

Question

MariaDB version is 10.4.34.

The query looks like:

SELECT bet.* FROM Bet bet WHERE bet.placed >= '2025-10-29T00:00:00' AND bet.placed <= '2025-10-29T23:59:59' AND EXISTS ( SELECT 1 FROM BetSelection bs WHERE bs.betIdFK = bet.recordId AND bs.sportId IN (50, 55) ) ORDER BY bet.placed DESC LIMIT 100;

A relation is one-to-many -> one bet can have a few selections. The Bet table has more than 100 x 10^6 records. The BetSelection table has 8.5 times more.

Selection from the Bet table is pretty fast:
- bet.recordId It is the primary key
- bet.placed is an indexed field

Selection from BetSelection is much worse:
- bs.betIdFK is a foreign key and is indexed
- bs.sportId It isn't indexed and has low cardinality. A lot of different sport IDs are presented, and they have pretty much duplicate rows.

Adding a single index for sportId doesn't look like a reasonable idea.

To speed up query execution, added a new composite index:
CREATE INDEX betIdFK_sportId_idx ON BetSelection(betIdFK, sportId);

However, found that after adding, it isn't used for selection. Explain shows that:
possibleKeys: betIdFK, betIdFK_sportId_idx
key: betIdFK

Thus, execution time is the same as before.

Why does this happen?
What is the best approach or strategy to speed up selection for this kind of query?

It can be rewritten as an inner join with distinct to exclude duplicates rather than group by.
However, it wouldn't have a real impact on speed. Checked with an inner join and found that the same query takes more time to execute compared to the exists clause.

And one more idea for thinking: The BetSelection table also has a field placed with the same value as the first one. Does it make sense to create an index for it and add it to the query, like the following:

SELECT bet.* FROM Bet bet WHERE bet.placed >= '2025-10-29T00:00:00' AND bet.placed <= '2025-10-29T23:59:59' AND EXISTS ( SELECT 1 FROM BetSelection bs WHERE bs.betIdFK = bet.recordId AND bs.sportId IN (40, 59, 88) AND bs.placed >= '2025-10-29T00:00:00' AND bs.placed <= '2025-10-29T23:59:59' ) ORDER BY bet.placed DESC LIMIT 100;

And only then consider any composite index to speed up.

Snippet from table BetSelection (InnoDB engine):

recordId bigint, betIdFK bigint null, sportId bigint null, placed datetime(3) null,

Update:

Found that index can be used when add directly to the query:

SELECT bet.* FROM Bet bet WHERE bet.placed >= '2025-10-29T00:00:00' AND bet.placed <= '2025-10-29T23:59:59' AND EXISTS (SELECT 1 FROM BetSelection bs use index (betIdFK_sportId_idx) WHERE bs.betIdFK = bet.recordId AND bs.sportId IN (20, 22)) ORDER BY bet.placed DESC LIMIT 100;

And here is the output with analayze of the previous query:

{ "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 182.58, "table": { "table_name": "bet", "access_type": "range", "possible_keys": ["PRIMARY", "betPlacedIndex"], "key": "betPlacedIndex", "key_length": "8", "used_key_parts": ["placed"], "r_loops": 1, "rows": 658444, "r_rows": 21709, "r_total_time_ms": 77.485, "filtered": 100, "r_filtered": 100, "attached_condition": "bet.placed >= '2025-10-29T00:00:00' and bet.placed <= '2025-10-29T23:59:59'" }, "table": { "table_name": "bs", "access_type": "ref", "possible_keys": ["betIdFK_sportId_idx"], "key": "betIdFK_sportId_idx", "key_length": "9", "used_key_parts": ["betIdFK"], "ref": ["bet.recordId"], "r_loops": 21709, "rows": 4, "r_rows": 7.6677, "r_total_time_ms": 92.606, "filtered": 100, "r_filtered": 0.0601, "attached_condition": "bs.sportId in (20,22)", "using_index": true, "first_match": "bet" } } }

As suggested suggestion with union all and found that for this version of MariaDB it has worth performance:

SELECT bet.* FROM Bet bet WHERE bet.placed >= '2025-10-29T00:00:00' AND bet.placed <= '2025-10-29T23:59:59' AND EXISTS (SELECT 1 FROM (SELECT 20 AS sportId UNION ALL SELECT 22) s JOIN BetSelection bs USE INDEX (betIdFK_sportId_idx) ON bs.betIdFK = bet.recordId AND bs.sportId = s.sportId) ORDER BY bet.placed DESC LIMIT 100;

Analayze details:

{ "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 251.6, "table": { "table_name": "bet", "access_type": "range", "possible_keys": ["betPlacedIndex"], "key": "betPlacedIndex", "key_length": "8", "used_key_parts": ["placed"], "r_loops": 1, "rows": 658444, "r_rows": 21709, "r_total_time_ms": 79.463, "filtered": 100, "r_filtered": 0.4606, "attached_condition": "bet.placed >= '2025-10-29T00:00:00' and bet.placed <= '2025-10-29T23:59:59' and <in_optimizer>(1,exists(subquery#2))" }, "subqueries": [ { "expression_cache": { "state": "disabled", "r_loops": 200, "r_hit_ratio": 0, "query_block": { "select_id": 2, "r_loops": 21709, "r_total_time_ms": 161.61, "table": { "table_name": "bs", "access_type": "ref", "possible_keys": ["betIdFK_sportId_idx"], "key": "betIdFK_sportId_idx", "key_length": "9", "used_key_parts": ["betIdFK"], "ref": ["bet.recordId"], "r_loops": 21709, "rows": 4, "r_rows": 7.6723, "r_total_time_ms": 93.8, "filtered": 100, "r_filtered": 100, "using_index": true }, "block-nl-join": { "table": { "table_name": "<derived3>", "access_type": "ALL", "r_loops": 31752, "rows": 2, "r_rows": 1.9973, "r_total_time_ms": 2.0159, "filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "404", "join_type": "BNL", "attached_condition": "bs.sportId = s.sportId", "r_filtered": 0.0301, "r_unpack_time_ms": 13.156, "materialized": { "query_block": { "union_result": { "query_specifications": [ { "query_block": { "select_id": 3, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 4, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } } } } } ] } }

Output for create tables:

CREATE TABLE `Bet` ( `recordId` bigint(20) NOT NULL AUTO_INCREMENT, `betId` varchar(50) DEFAULT NULL, `betType` varchar(255) DEFAULT NULL, `live` bit(1) DEFAULT b'1', `betBuilder` bit(1) DEFAULT NULL, `operatorId` bigint(20) DEFAULT NULL, `overallBetCurrency` varchar(5) DEFAULT NULL, `overallBetPlacementStatus` varchar(10) DEFAULT NULL, `placed` datetime(3) DEFAULT NULL, `settlementStatus` varchar(10) DEFAULT NULL, `terminalType` int(11) DEFAULT NULL, `totalBetStakeEur` double DEFAULT NULL, `userId` bigint(20) DEFAULT NULL, `userName` varchar(255) DEFAULT NULL, `placementStatusConfirmed` datetime(3) DEFAULT NULL, `placement_type` int(11) DEFAULT 0, PRIMARY KEY (`recordId`), UNIQUE KEY `betIdIndex` (`betId`), KEY `betPlacedIndex` (`placed`), KEY `operatorIdIndex` (`operatorId`), KEY `userIdIndex` (`userId`) ) ENGINE=InnoDB AUTO_INCREMENT=957349657 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED CREATE TABLE `BetSelection` ( `recordId` bigint(20) NOT NULL AUTO_INCREMENT, `automaticSelectionSettlementStatus` varchar(10) DEFAULT NULL, `betId` varchar(50) DEFAULT NULL, `manualSelectionSettlementStatus` varchar(10) DEFAULT NULL, `operatorId` bigint(20) DEFAULT NULL, `overallBetCurrency` varchar(5) DEFAULT NULL, `overallBetLastPayoutTransactionId` varchar(100) DEFAULT NULL, `overallBetPayout` double DEFAULT NULL, `overallBetPlacementStatus` varchar(10) DEFAULT NULL, `overallBetSettlementStatus` varchar(10) DEFAULT NULL, `overallBetStake` double DEFAULT NULL, `placed` datetime(3) DEFAULT NULL, `selectionEventId` bigint(20) DEFAULT NULL, `selectionEventPartId` bigint(20) DEFAULT NULL, `selectionOdds` double DEFAULT NULL, `betBuilderOdds` double DEFAULT NULL, `selectionOutcomeId` bigint(20) DEFAULT NULL, `settled` datetime(3) DEFAULT NULL, `systemBetReferenceId` varchar(50) DEFAULT NULL, `userId` bigint(20) DEFAULT NULL, `userRatio` double DEFAULT NULL, `overallCashOutValue` double DEFAULT NULL, `selectionBettingTypeId` bigint(20) DEFAULT NULL, `selectionEventLocationId` bigint(20) DEFAULT NULL, `selectionParentEventId` bigint(20) DEFAULT NULL, `sportId` bigint(20) DEFAULT NULL, `selectionOutcomeTextId` varchar(256) DEFAULT NULL, `eventId` bigint(20) DEFAULT NULL, `overallBetStakeEur` double DEFAULT NULL, `extraBetInfo` longtext DEFAULT NULL, `outcomeSettlementTime` datetime(3) DEFAULT NULL, `selectionSettlementTime` datetime(3) DEFAULT NULL, `overallSystemBetSettlementStatus` varchar(10) DEFAULT NULL, `maxPrice` double DEFAULT NULL, `maxWinning` double DEFAULT NULL, `selectionAwayParticipantId` bigint(20) DEFAULT NULL, `selectionEventName` varchar(255) DEFAULT NULL, `selectionEventStartTimeEpochMillis` bigint(20) DEFAULT NULL, `selectionHomeParticipantId` bigint(20) DEFAULT NULL, `systemBetType` int(11) DEFAULT NULL, `userName` varchar(255) DEFAULT NULL, `betType` varchar(10) DEFAULT NULL, `betGroupId` varchar(50) DEFAULT NULL, `eachWay` bit(1) DEFAULT NULL, `extraSelectionInfo` longtext DEFAULT NULL, `possibleProfit` double DEFAULT NULL, `possibleProfitEur` double DEFAULT NULL, `selectionOrderNumber` bigint(20) DEFAULT NULL, `actualSelectionSettlementStatus` varchar(10) DEFAULT NULL, `overallSystemBetPayout` double DEFAULT NULL, `selectionActualStatusSettlementTime` datetime(3) DEFAULT NULL, `systemBetSettlementTime` datetime(3) DEFAULT NULL, `betIdFK` bigint(20) DEFAULT NULL, `overallBetCashOutDate` datetime(3) DEFAULT NULL, `selectionCashOutOdds` double DEFAULT NULL, `selectionCashOutBetBuilderOdds` double DEFAULT NULL, `cashOutSelectionSettlementStatus` varchar(10) DEFAULT NULL, `settlementUpdateId` varchar(36) DEFAULT NULL, `settlementVersion` bigint(20) DEFAULT NULL, PRIMARY KEY (`recordId`), KEY `betIdAndSystemBetReferenceIdIndex` (`betId`,`systemBetReferenceId`), KEY `betIdIndex` (`betId`), KEY `FK_5rvexp5scsd1d3owd716uh69m` (`eventId`), KEY `IDX_BetSelection_selectionEven` (`selectionEventId`), KEY `selectionOutcomeIdIndex` (`selectionOutcomeId`), KEY `systemBetReferenceIdIndex` (`systemBetReferenceId`), KEY `selectionOutcomeIdAndoverallBetPlacementStatusIndex` (`selectionOutcomeId`,`overallBetPlacementStatus`), KEY `FK8csgjb0ykjsngvj734q4swpb2` (`betIdFK`), KEY `updateAutomaticSettlementIndexWithEachWay` (`selectionOutcomeId`,`overallBetPlacementStatus`,`eachWay`,`automaticSelectionSettlementStatus`,`settlementVersion`), KEY `updateAutomaticSettlementIndex` (`selectionOutcomeId`,`overallBetPlacementStatus`,`automaticSelectionSettlementStatus`,`settlementVersion`,`possibleProfitEur`), KEY `settlementUpdateIdIndex` (`settlementUpdateId`), KEY `betIdFK_sportId_idx` (`betIdFK`,`sportId`) ) ENGINE=InnoDB AUTO_INCREMENT=5347802087 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=377 ROW_FORMAT=COMPRESSED

Answer 1 · 2025-11-08 10:48:03Z

Have you tried to force the optimizers hand? https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/force-index

Answer 2 · 2025-11-09 01:56:57Z

OR or IN conditions aren't very effective as part of a composite index; rewrite using join if you want that:

select 1 from (select 50 sportId union all select 55) sports join BetSelection bs on bs.betIdFK = bet.recordId AND bs.sportId = sports.sportId

Once you get it using your composite index, yes, add the date and join bet from the bet selection query.

Answer 3 · 2025-11-09 22:29:45Z

Hm, I'm not seeing the used key parts/key length be what it should be. Looks like it is using the sports id as a filter but not actually part of the index lookup. Can you please include output of show create table Bet; and show create table BetSelection;?

Answer 4 · 2025-11-10 01:48:17Z

"Why does this happen?" - When you have INDEX(a) and INDEX(a,b), the Optimizer may take the shorter index even when the longer would be better. So, drop the index on just `betIdFK`.

Answer 5 · 2025-11-10 01:58:32Z

`datetime(3)` vs "23:59:59" --> you are missing some bets in the last second before midnight. I recommend this syntax instead:

 bet.placed >= '2025-10-29' AND bet.placed < '2025-10-29` + INTERVAL 1 DAY`

Answer 6 · 2025-11-10 02:13:01Z

Let's reformulate it to put the LIMIT inside. This should avoid hauling around "bet.*" past the 100 rows needed.

SELECT bet2.* FROM ( SELECT bet1.recordId, bet1.placed FROM Bet as bet1 WHERE bet1.placed >= '2025-10-29' AND bet1.placed < '2025-10-29' + INTERVAL 1 DAY AND EXISTS ( SELECT 1 FROM BetSelection bs WHERE bs.betIdFK = bet1.recordId AND bs.sportId IN (50, 55) ORDER BY bet1.placed DESC LIMIT 100 ) AS x JOIN Bet as bet2 USING(recordId) ORDER BY x.placed DESC; -- yes, repeated

Collectives™ on Stack Overflow

Why MariaDB doesn't use newly added index?

6 Replies 6