Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.13
-
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,
- if the "order by" fields are part of Primary keys then MariaDB uses the Index Scan and there is no performance issue
- 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