MariaDB bad index choosen after threshold

Question

I'm working with MariaDB -> 10.11.4-MariaDB-1:10.11.4+maria~ubu2204

I have a table with :

• 7,984,176 rows
• 2 columns :
  • lab_id (ID of a laboratory)
  • date_prelevement : date of the event from 1 year old to now, every date is distincte
• lab_id is foreign key to an other table ent_laboratoire with 14 distincts values, with this repartition :

enter image description here

2 indexes :

create index restst_labid on res_ech_test_alex (lab_id);
create index restst_dtprel on res_ech_test_alex (date_prelevement);

The query :

select *
      from res_ech_test_alex res
          ,ent_laboratoire el 
     where res.lab_id = el.id 
       and res.date_prelevement between ( now() - interval 90 DAY )  
                                and ( now() - interval 70 DAY );

90 and 70 are moving.

Results of performances tests :

enter image description here

I see a threshold between 17 days and 18 days queried : 1.7 seconds to 16 seconds.

When a FORCE index on date with 18 days of interval -> 1.7 seconds.

How can i change this threshold without FORCE index (because i don't like that and my front dev could not do that with his ORM) ? I thought about optimizer parameters.

Regards, Alexandre

See description with details.