MySQL Forums
Forum List  »  Performance

Re: Bad order by performance (5.7.12 under Aurora)
Posted by: Frederic Descamps
Date: November 27, 2019 04:21AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Bad order by performance (5.7.12 under Aurora)
228
November 27, 2019 04:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.