There is a sign - 1 million people, there are different data, foreign keys, and so on. It is necessary to quickly (as far as possible) select from the table data sorted by full name.

Slightly read about the indices that improve the sample. Made an index BTREE f_name + i_name + o_name, and BTREE index for each field. With EXPLAIN, no index is used. Even with ORDER BY f_name (one field), nothing is used.

Tell me, please, how to be.

UPD1:

CREATE TABLE `table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `group_id` int(10) unsigned NOT NULL DEFAULT '0', `division_id` int(10) unsigned DEFAULT NULL, `parent_id` int(10) unsigned DEFAULT NULL, `category_id` int(10) unsigned DEFAULT NULL, `f_name` varchar(255) NOT NULL DEFAULT '', `i_name` varchar(255) NOT NULL DEFAULT '', `o_name` varchar(255) DEFAULT NULL, `birth_date` date DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `phone` varchar(255) DEFAULT NULL, `mail` varchar(255) DEFAULT NULL, `status` varchar(45) DEFAULT NULL, `check` tinyint(1) unsigned NOT NULL DEFAULT '0', `addr_object_id` char(36) DEFAULT NULL, `addr_house_id` char(36) DEFAULT NULL, `addr_room` char(36) DEFAULT NULL, `place_id` int(10) unsigned DEFAULT NULL, `check_from` datetime DEFAULT NULL, `check_to` datetime DEFAULT NULL, `check_period` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_table_1` (`group_id`), KEY `FK_table_2` (`division_id`), KEY `FK_table_3` (`parent_id`), KEY `FK_table_4` (`place_id`), KEY `FK_table_5` (`category_id`), KEY `Index_F` (`f_name`), KEY `Index_I` (`i_name`), KEY `Index_FIO` (`f_name`,`i_name`) USING BTREE, KEY `Index_division` (`division_id`), CONSTRAINT `FK_table_1` FOREIGN KEY (`group_id`) REFERENCES `groups` `id`), CONSTRAINT `FK_table_2` FOREIGN KEY (`division_id`) REFERENCES `divisions` (`id`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `FK_table_3` FOREIGN KEY (`parent_id`) REFERENCES `table` (`id`), CONSTRAINT `FK_table_4` FOREIGN KEY (`place_id`) REFERENCES `places` (`id`), CONSTRAINT `FK_table_5` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=398351 DEFAULT CHARSET=utf8

Request:

 EXPLAIN SELECT * FROM table ORDER BY f_name

Answer:

 id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, 'SIMPLE', 'cooperators', 'ALL', '', '', '', '', 383343, 'Using filesort'
  • Not enough data for a meaningful response. The exact structure of the table, information about the content and variability, the text of the query and its EXPLAIN are necessary. - Akina
  • @Akina, added something to the question - Skywave
  • @Akina, what content information is needed? These are a variety of different colors - Skywave
  • The structure of the table is the conclusion of SHOW CREATE TABLE , and not the “essay on the topic” ... - Akina
  • one
    Well, it remains to be concluded that, according to the optimizer, it is cheaper to scan the table without using an index. That when getting all the fields of all records without selection, probably close to the truth ... Try adding the FORCE INDEX FOR ORDER BY (`Index_F`) . Ps. A 255 characters under the last name or is there a phone number - is there any idea behind this? - Akina

1 answer 1

For the KEY key Index_FIO (f_name, i_name, o_name) - increase each field to 50.

 KEY `Index_FIO` (f_name(50), i_name(50), o_name(50));

The type of index is not very important.

Then do

 EXPLAIN SELECT * FROM table ORDER BY f_name, i_name, o_name

and tell me what happened.

  • The result is the same. I made the explain request explain select * from table where f_name = 'Ivanov' and i_name = 'Ivan' order by f_name, i_name - explain showed that the Index_FIO index was used. it turns out that it is not used until there are restrictions, where it would help (logical), but when I write limit 10000, 100 - it is not used. When sorting by id - the PRIMARY key is used even without conditions - Skywave
  • on limit 5000, 100 the key is being used, maybe I do not have enough RAM to use the key at a higher limit ... or something like that. 4GB Memory - Skywave
  • Yes you are right. Useless. - Mrak