MariaDB left join ignores index key - slow performance with large joined table

Question

I have a select with a large joined table. On local, performance is fine, but on my server it takes much longer. Below are the version infos (I just upgraded to MariaDB 11.4 on the server).

If I do an explain select comparison I see that on the server, the 'type' is 'ALL' instead of 'ref'. Why is this so, how can I change it and improve performance?

Query with Explain:

explain select 
    `lei_portfolios_mm`.`id` as `lei_portfolios_mm.id`, 
    `lei_portfolios_mm`.`portfolio_id` as `lei_portfolios_mm.portfolio_id`, 
    `lei_portfolios_mm`.`lei` as `lei_portfolios_mm.lei`, 
    `lei_portfolios`.`id` as `lei_portfolios.id`, 
    `lei_portfolios`.`name` as `lei_portfolios.name`, 
    `lei`.`id` as `lei.id`, 
    `lei`.`lei` as `lei.lei` 
from `lei_portfolios_mm` 
left join `lei_portfolios` on `lei_portfolios`.`id` = `lei_portfolios_mm`.`portfolio_id` 
left join `lei` on `lei`.`lei` = `lei_portfolios_mm`.`lei`;

Local:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  lei_portfolios_mm   ALL     NULL    NULL    NULL    NULL    373     
1   SIMPLE  lei_portfolios  eq_ref  PRIMARY     PRIMARY     4   db.lei_portfolios_mm.portfolio_id   1   Using where
1   SIMPLE  lei     ref     lei     lei     502     db.lei_portfolios_mm.lei    1   Using where

Server:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  lei_portfolios_mm   ALL     NULL    NULL    NULL    NULL    374     
1   SIMPLE  lei_portfolios  eq_ref  PRIMARY     PRIMARY     4   db.lei_portfolios_mm.portfolio_id   1   Using where
1   SIMPLE  lei     ALL     NULL    NULL    NULL    NULL    2818384     Using where; Using join buffer (flat, BNL join)

Query without Explain:

select 
   `lei_portfolios_mm`.`id` as `lei_portfolios_mm.id`,
   `lei_portfolios_mm`.`portfolio_id` as `lei_portfolios_mm.portfolio_id`,
   `lei_portfolios_mm`.`lei` as `lei_portfolios_mm.lei`, 
   `lei_portfolios`.`id` as `lei_portfolios.id`,
   `lei_portfolios`.`name` as `lei_portfolios.name`,
   `lei`.`id` as `lei.id`,
   `lei`.`lei` as `lei.lei`
from `lei_portfolios_mm` 
left join `lei_portfolios` on `lei_portfolios`.`id` = `lei_portfolios_mm`.`portfolio_id` 
left join `lei` on `lei`.`lei` = `lei_portfolios_mm`.`lei`;

Local:

Showing rows 0 - 24 (374 total, Query took 0.0043 seconds.)

Server:

Showing rows 0 - 24 (374 total, Query took 54.0806 seconds.)

What I don't get is why there is such a significant difference (local vs server) in performance. The structure of the tables and defined keys should be the same. The only thing I found so far is that if I add 'explain' and compare the results, for the server it shows '1 SIMPLE lei ALL ...' ... Why is it type 'ALL' (full table scan) on the server and on local 'ref' (non unique index) which I'm guessing causes the issue?! https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain

Query:

show create table `lei`;

Local:

CREATE TABLE `lei` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lei` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lei` (`lei`)
) ENGINE=InnoDB AUTO_INCREMENT=2951846 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Server:

CREATE TABLE `lei` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lei` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lei` (`lei`)
) ENGINE=InnoDB AUTO_INCREMENT=2951846 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

Query:

show create table `lei_portfolios_mm`;

Local:

CREATE TABLE `lei_portfolios_mm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `portfolio_id` varchar(25) NOT NULL,
  `lei` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Server:

CREATE TABLE `lei_portfolios_mm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `portfolio_id` varchar(25) NOT NULL,
  `lei` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

MariaDB-Versions:

Local:

Server type: MariaDB
Server version: 10.6.22-MariaDB-0ubuntu0.22.04.1 - Ubuntu 22.04

Server:

Server type: MariaDB
Server version: 11.4.7-MariaDB-ubu2204 - mariadb.org binary distribution 
    Server version: 10.6.18-MariaDB-0ubuntu0.22.04.1 - Ubuntu 22.04

Aside: Get in the habit of using table aliases so you don't have to repeat long names like lei_portfolios a dozen times in the query. — Barmar
– Barmar, Commented Jul 10 at 18:29
Index use depends on its cardinality, which depends on the data that happens to be in the table. — Barmar
– Barmar, Commented Jul 10 at 18:33
What is query plan if join only lei_portfolios left join lei on lei.lei = lei_portfolios_mm.lei — ValNik
– ValNik, Commented Jul 10 at 19:51
What I don't get is why there is such a significant difference (local vs server) in performance. The structure of the tables and defined keys should be the same. The only thing I found so far is that if I add 'explain' and compare the results, for the server it shows '1 SIMPLE lei ALL ...' ... Why is it type 'ALL' (full table scan) on the server and on local 'ref' (non unique index) which I'm guessing causes the issue?! — Philipp M
– Philipp M, Commented Jul 10 at 20:30
Can you update your question to show output of show create table lei and show create table lei_portfolios_mm? And verify those are the same in local and server? — ysth
– ysth, Commented Jul 11 at 2:31

Philipp M · Accepted Answer · 2025-07-11 10:35:58Z

2

The problem was that the CHARSET & COLLATE was different. What solved it was changing the lei table from utf8mb3 to utf8mb4 which I missed previously:

ALTER TABLE lei CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

answered Jul 11 at 10:35

Philipp M

3,5485 gold badges46 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick James Jul 14 at 2:35

We can't see the details since you deleted the CREATE TABLEs. Lots of programmers will read your Q&A and be baffled at what the problem was.

Philipp M Jul 14 at 14:36

I added back the show create and server type infos

JJ Ward · Accepted Answer · 2025-07-11 02:40:44Z

0

The difference is that there doesn't appear to be a foreign key relationship between lei.lei and lei_portfolios_mm.lei on the server like there is on your local instance, as indicated by the differences in the lei JOIN of the EXPLAIN output:

1   SIMPLE  lei     ref     lei     lei     502     db.lei_portfolios_mm.lei    1   Using where

1   SIMPLE  lei     ALL     NULL    NULL    NULL    NULL    2818384     Using where; Using join buffer (flat, BNL join)

This leaves the query planner on the server using full table scans (as indicated by the "ALL" JOIN type) in a Batch Nested-Loop ("BNL join") - an algorithm known for being slow and a last resort in most cases - to try and parse the relation versus the simple index lookup being used on your local instance.

Check the DDL definitions for the lei and lei_portfolios_mm tables on both your local machine and on the server - I'd assume that lei is the reference table, so ALTERing lei_portfolios_mm on the server to add a foreign key on lei to the reference lei.lei column should fix the query planner's blind spot:

USE db;
ALTER TABLE lei_portfolios_mm ADD CONSTRAINT fk_lei FOREIGN KEY (lei) REFERENCES lei(lei);

answered Jul 11 at 2:40

JJ Ward

14210 bronze badges

2 Comments

Philipp M Jul 11 at 7:18

Unfortunately I'm getting an ERROR 1005 (HY000): Can't create table db.lei_portfolios_mm (errno: 150 "Foreign key constraint is incorrectly formed")

JJ Ward Jul 13 at 19:51

Ah yep, I was going to suggest checking charset, collation, and data type/length were the same (which fixes this particular error but also the overall inability to match indexing/columns).

Collectives™ on Stack Overflow

MariaDB left join ignores index key - slow performance with large joined table

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related