Comments - Indexing on low cardinality columns is degrading the performance
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
First note that the 10.7 release series is at the end of life.
The 11.0 release series and newer, like the 11.4 LTS release series of MariaDB have improved optimizer functions capable of handling the OR criteria of your JOIN in a much better way estimating the cost better and likely to generate a better query plan.
Yes low cardinality should usually be shunned, except in the case where the dataset of the table can be retreived from just the index.
Using ANALYZE FORMAT=JSON {query} can help see where either r_ amount, the real, differers from the estimate without the r_ prefix.
Optimizer trace allows a trace of how the query plan was made.
Strongly recommend updating to the 11.4 version in a test environment and measuring how well or otherwise the query performs there. Include ANALYZE or optimizer trace if still getting suboptimal queries.