Sorted queries on a large SQL table are slow despite an index exists-CodePudding

I have a very large SQL Table (More than 3 mio rows) comment_field_data.

    CREATE TABLE `comment_field_data` (
  `cid` int(10) unsigned NOT NULL,
  `comment_type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `langcode` varchar(12) CHARACTER SET ascii NOT NULL,
  `status` tinyint(4) NOT NULL,
  `uid` int(10) unsigned NOT NULL COMMENT 'The ID of the target entity.',
  `pid` int(10) unsigned DEFAULT NULL COMMENT 'The ID of the target entity.',
  `entity_id` int(10) unsigned DEFAULT NULL COMMENT 'The ID of the target entity.',
  `subject` varchar(64) DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL,
  `mail` varchar(254) DEFAULT NULL,
  `homepage` varchar(255) DEFAULT NULL,
  `hostname` varchar(128) DEFAULT NULL,
  `created` int(11) NOT NULL,
  `changed` int(11) DEFAULT NULL,
  `thread` varchar(255) NOT NULL,
  `entity_type` varchar(32) CHARACTER SET ascii NOT NULL,
  `field_name` varchar(32) CHARACTER SET ascii NOT NULL,
  `default_langcode` tinyint(4) NOT NULL,
  PRIMARY KEY (`cid`,`langcode`),
  KEY `comment__id__default_langcode__langcode` (`cid`,`default_langcode`,`langcode`),
  KEY `comment_field__comment_type__target_id` (`comment_type`),
  KEY `comment_field__uid__target_id` (`uid`),
  KEY `comment_field__created` (`created`),
  KEY `comment__status_comment_type` (`status`,`comment_type`,`cid`),
  KEY `comment__status_pid` (`pid`,`status`),
  KEY `comment__num_new` (`entity_id`,`entity_type`,`comment_type`,`status`,`created`,`cid`,`thread`(191)),
  KEY `comment__entity_langcode` (`entity_id`,`entity_type`,`comment_type`,`default_langcode`)
)

Queries like

SELECT entity_id FROM comment_field_data ORDER BY created LIMIT 300

perform very poorly (several seconds) - and

EXPLAIN SELECT entity_id FROM comment_field_data ORDER BY created LIMIT 300;
 ------ ------------- -------------------- ------- --------------- ------------------ --------- ------ --------- ----------------------------- 
| id   | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows    | Extra                       |
 ------ ------------- -------------------- ------- --------------- ------------------ --------- ------ --------- ----------------------------- 
|    1 | SIMPLE      | comment_field_data | index | NULL          | comment__num_new | 848     | NULL | 3384043 | Using index; Using filesort |
 ------ ------------- -------------------- ------- --------------- ------------------ --------- ------ --------- -----------------------------

Shows that filesort is used despite an existing index for the created column. I'm not an SQL expert at all and I don't know what ca do to improve this behavior. Can anyone help out?

CodePudding user response：

you should try to change the index key :

ALTER TABLE comment_field_data
  DROP KEY `comment_field__created`, 
  ADD KEY `comment_field__created` (`created`, `entity_id`) ;

To "encourage" MariaDB to use this new index, you may change your query like this :

SELECT entity_id 
FROM comment_field_data 
WHERE created > '1970-01-01'
AND entity_id > 0
ORDER BY created LIMIT 300

Hope this will help

This technique is named, index covering, which means, the index structure contains all the columns invoked in the query.

Using this index, to execute your query, MariaDB will not have to do extra I/Os reading the data layer contained in the primary key.