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

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

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

Solution

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;