I have a simple query running in MariaDB 10.4.12:

SELECT *
FROM transazioni tr
LEFT JOIN acqurienti a ON tr.acquirente=a.id
WHERE
tr.frontend=1
AND tr.stato!=0
AND a.cognome LIKE 'AnyText%'
ORDER BY tr.creazione

This table has these indexes:
frontend int field with cardinality 166
creazione datatime field with cardinality 123541 (all rows of the table)

And this is its Explain:

+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+
| id  | select_type  | table  |  type   |             possible_keys               |    key     | key_len  |                ref                 | rows  |    Extra    |
+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+
|  1  | SIMPLE       | tr     | index   | acquirente,frontend                     | creazione  |       6  | NULL                               |  793  | Using where |
|  1  | SIMPLE       | a      | eq_ref  | PRIMARY                                 | PRIMARY    |       4  | tr.acquirente                      |    1  | Using where |
+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+

For some reason it takes about 12s because it uses creazione index that is a datetime.

If I remove ORDER BY creazione it speed up to 0.4s and its explain changes to:

+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+
| id  | select_type  | table  |  type   |             possible_keys               |        key          | key_len  |      ref       | rows  |    Extra    |
+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+
|  1  | SIMPLE       | tr     | ref     | acquirente,frontend                     | frontend            |       5  | const          | 3112  | Using where |
|  1  | SIMPLE       | a      | eq_ref  | PRIMARY                                 | PRIMARY             |       4  | tr.acquirente  |    1  | Using where |
+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+

I cannot understand why the key changes due to ORDER BY keyword switching to a worse cardinality. As far as I know the ORDER is applied after the where, and here for this example the where filters limits the resultset to only 2 rows.

I know a solution can be change the frontend index to (frontend,creazione) and remove the single creazione index, but I’m missing the logic of key selection. And moreover, I may need the single creazione index for some other queries.

Why the order by deceives the index selection?