[MDEV-23978] Select Query with order by on indexed columns is using filesort Created: 2020-10-19  Updated: 2021-10-06

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.13
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Lakshmi Praveena Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None


 Description   
CREATE TABLE `test` (
`code_value` char(9) NOT NULL,
`id` char(36) NOT NULL,
`office_id_number` char(4) DEFAULT '0002',
`address` varchar(150) DEFAULT NULL,
`city` varchar(75) DEFAULT NULL,
`state` varchar(10) DEFAULT NULL,
`zip_code` varchar(15) DEFAULT NULL,
`record_type` char(1) NOT NULL,
`contact_name` varchar(150) DEFAULT NULL,
`sequence` int(11) DEFAULT NULL,
PRIMARY KEY (`code_value`,`record_type`),
KEY `test_idx1` (`record_type`,`sequence`,`code_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The below Query uses filesort and doesn't use Index scan (The index is `test_idx1` and was created on (`record_type`,`sequence`,`code_value`) )

explain
select code_value, office_id_number, address, city, state, zip_code,
record_type, contact_name, DATE_FORMAT(now(), "%m%d%Y")
from test
order by record_type, sequence, code_value

Explain Plan -

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test ALL NULL NULL NULL NULL 52402 Using filesort

The below Query uses Index scan (may be based of Primary Key)

explain
select code_value, office_id_number, address, city, state, zip_code,
record_type, contact_name, DATE_FORMAT(now(), "%m%d%Y")
from test
order by code_value, record_type

Explain Plan -

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test index NULL PRIMARY 30 NULL 52402  

I think the behavior is,

  1. if the "order by" fields are part of Primary keys then MariaDB uses the Index Scan and there is no performance issue
  2. if the "order by" fields are not part of Primary keys then; MariaDB uses filesort though a composite index is created. Use of filesort hits the performance

 Comments   
Comment by Lakshmi Praveena [ 2020-10-27 ]

Is it possible to expedite this ticket ? i believe, this is a open bug in all MariaDB versions

Comment by Sergei Petrunia [ 2021-03-19 ]

The below Query uses filesort and doesn't use Index scan (The index is `test_idx1` and was created on (`record_type`,`sequence`,`code_value`) )

explain
 
select code_value, office_id_number, address, city, state, zip_code,
 
record_type, contact_name, DATE_FORMAT(now(), "%m%d%Y")
 
from test
 
order by record_type, sequence, code_value

This makes sense. The ORDER BY clause matches the index test_idx1, but the query reads columns that are not part of that index. The query has no WHERE clause, so it will need to read all rows. Reading all rows through the secondary index will cause random index lookups to be made when fetching the row data. This is typically slower than doing a sequential read + sorting. The optimizer makes use of this assumption and so does not consider an index-based plan.

The below Query uses Index scan (may be based of Primary Key)

Right. In innodb, the primary key is clustered, so doing a full table scan is the same as doing a scan on PK. That is, we get rows in PK order for free. The optimizer makes use of this property and avoids sorting.

Comment by Sergei Petrunia [ 2021-03-19 ]

I don't consider this behavior to be a bug.

  • It might be possible that using test_idx1 is faster in some circumstances (e.g. where all data is in memory) but it is not advantageous in others.
  • Considering we have to read and return all rows (already expensive), does sorting really add significant overhead?

Feel free to reopen if you can show this is really a performance problem.

Comment by Lakshmi Praveena [ 2021-03-22 ]

Thanks @sergei petrunia for these details. i have about 60k records on the table and yes, its taking about 15-20 minutes to read all rows in sorted manner. I would like to re-open this bug because the performance problem do exists

My requirement, is real all rows (~ 60k) on the table and write to file in a sorted manner. Please let me what else can be done. Thanks !

Comment by Lakshmi Praveena [ 2021-03-22 ]

@sergei petrunia Btw, I'm not able to re-open this bug. Appreciate your help, if you can re-open this bug on behalf of me

Comment by Sergei Petrunia [ 2021-03-23 ]

(Just pressed the re-open button for now. Will get back to analyzing the provided info)

Comment by Lakshmi Praveena [ 2021-03-23 ]

Thanks much Sergei Petrunia

Comment by Lakshmi Praveena [ 2021-10-06 ]

Any updates on this would be highly appreciated !!

Generated at Thu Feb 08 09:26:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.