MariaDB

Why ORDER BY FNAME not use index "KEY `FNAME` (`FNAME`(255))" ??

Asked by Mikhail Gavrilov

CREATE TABLE `crm_client` (
  `id_client` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `SNAME` VARCHAR(700) DEFAULT NULL,
  `FNAME` VARCHAR(700) DEFAULT NULL,
  `client_type` INT(10) NOT NULL,
  `INN` VARCHAR(50) DEFAULT NULL,
  `res` ENUM('R','N') DEFAULT NULL,
  `emp_id_first` INT(10) DEFAULT NULL,
  `fdate` DATE DEFAULT NULL,
  `mdate` TIMESTAMP NULL DEFAULT NULL,
  `udate` TIMESTAMP NULL DEFAULT NULL,
  `comment` LONGTEXT,
  `terrorist` INT(10) DEFAULT NULL,
  `risk_type` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_str` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_oper` ENUM('normal','high','critical') DEFAULT NULL,
  `risk_mark` LONGTEXT,
  `ank_period` INT(10) DEFAULT NULL,
  `emp_id` INT(10) DEFAULT NULL,
  `resCntr` VARCHAR(2) DEFAULT NULL,
  `foreign_public` INT(10) DEFAULT NULL,
  `promotion` TINYINT(1) DEFAULT NULL,
  `contr_agent` INT(10) DEFAULT NULL,
  `id_sale` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id_client`),
  KEY `INN` (`INN`),
  KEY `crm_client_ibfk_1` (`client_type`),
  KEY `FNAME` (`FNAME`(255)),
  CONSTRAINT `crm_client_ibfk_1` FOREIGN KEY (`client_type`) REFERENCES `crm_client_type` (`client_type`) ON DELETE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=237472 DEFAULT CHARSET=utf8

EXPLAIN SELECT
  *
FROM
  crm_client
ORDER BY FNAME
LIMIT 9

    id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ---------- ------ ------------- ------ ------- ------ ------ ----------------
     1 SIMPLE crm_client ALL (NULL) (NULL) (NULL) (NULL) 224874 Using filesort

Question information

Language:
English Edit question
Status:
Answered
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Mikhail Gavrilov (mikegav) said : 		#1

Seems index with order not work if length of varchar field more than 255 chars. :(

MariaDB 10.0.7

Revision history for this message
Sergei Golubchik (sergii) said : 		#2

Because the column is FNAME VARCHAR(700) and the index is KEY(FNAME(255)). Reading rows from the index will not produce them in the correct sorted order.

For example (with smaller numbers), you have a column F VARCHAR(10), and an index KEY(F(3)). The values as in

   aaaaaa
   abc54321
   abc12345
   def000
   xyz000

they are sorted according to the KEY(F(3)), that is, sorted by the first three letters. But if you need ORDER BY F, that is, if you need to sort by the whole F length, the index order won't be correct.

Can you help with this problem?

Provide an answer of your own, or ask Mikhail Gavrilov for more information if necessary.

To post a message you must log in.