Re: Bad order by performance (5.7.12 under Aurora)

[Frederic Descamps]

Hi Nicola,

Yes it's cost base. However, the problem you are facing here is that the temporary table is usually not problematic (it should even limit it to the 200 records from your limit). And even with an index for the sorted column will require that temp table. Your issue is related to the LONGTEXT field you have. Aurora as MySQL before 8.0, won't be able to use in memory internal temp tables when blog/text fields are used. From the manual:

Presence of a BLOB or TEXT column in the table. However, the TempTable storage engine, which is the default storage engine for in-memory internal temporary tables in MySQL 8.0, supports binary large object types as of MySQL 8.0.13. See Internal Temporary Table Storage Engine.

So I recommend you once again to upgrade to MySQL 8.0 ;)

This is the output of your query using EXPLAIN ANALYZE in MySQL 8.0.18 (without any data):

| -> Sort row IDs: <temporary>.ultima_elaborazione
-> Table scan on <temporary>
-> Temporary table
-> Group (no aggregates)
-> Filter: (_0009_.id is null) (cost=3.15 rows=1)
-> Nested loop anti-join (cost=3.15 rows=1)
-> Nested loop left join (cost=2.80 rows=1)
-> Filter: (_0007_.id is null) (cost=2.45 rows=1)
-> Nested loop anti-join (cost=2.45 rows=1)
-> Nested loop left join (cost=2.10 rows=1)
-> Filter: (_0005_.id is null) (cost=1.75 rows=1)
-> Nested loop anti-join (cost=1.75 rows=1)
-> Nested loop left join (cost=1.40 rows=1)
-> Nested loop left join (cost=1.05 rows=1)
-> Nested loop left join (cost=0.70 rows=1)
-> Index scan on _fatture_passive using PRIMARY (cost=0.35 rows=1)
-> Index lookup on relations using record (record=_fatture_passive.id) (cost=0.35 rows=1)
-> Single-row index lookup on meta using PRIMARY (id=_fatture_passive.id) (cost=0.35 rows=1)
-> Filter: ((_0004_.`table` = '_fatture_passive') and (_0004_.field = 'amministratore')) (cost=0.35 rows=1)
-> Index lookup on _0004_ using record (record=_fatture_passive.id) (cost=0.35 rows=1)
-> Filter: (_0005_.id = _0004_.`value`) (cost=0.35 rows=1)
-> Single-row index lookup on _0005_ using PRIMARY (id=_0004_.`value`) (cost=0.35 rows=1)
-> Filter: ((_0006_.`table` = '_fatture_passive') and (_0006_.field = 'condominio')) (cost=0.35 rows=1)
-> Index lookup on _0006_ using record (record=_fatture_passive.id) (cost=0.35 rows=1)
-> Filter: (_0007_.id = _0006_.`value`) (cost=0.35 rows=1)
-> Single-row index lookup on _0007_ using PRIMARY (id=_0006_.`value`) (cost=0.35 rows=1)
-> Filter: ((_0008_.`table` = '_fatture_passive') and (_0008_.field = 'blacklist')) (cost=0.35 rows=1)
-> Index lookup on _0008_ using record (record=_fatture_passive.id) (cost=0.35 rows=1)
-> Filter: (_0009_.id = _0008_.`value`) (cost=0.35 rows=1)
-> Single-row index lookup on _0009_ using PRIMARY (id=_0008_.`value`) (cost=0.35 rows=1)