Question 221409 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – query is highly performant without an ORDER BY, slows to a crawl with an ORDER BY

Here’s my query:

SELECT n.id, n.member_id, n.content, n.created_at
FROM notes n
JOIN note_metadata nm ON n.id = nm.note_id
    AND nm.meta_key_id = 4 # Nature
    AND nm.meta_value = 'Cancellation'
#ORDER BY n.id DESC
LIMIT 10;

Without the ORDER BY the query is very performant. Here’s what the EXPLAIN looks like for that:

enter image description here

With the ORDER BY, however, the query slows down to a crawl. Here’s what the EXPLAIN looks like for that:

enter image description here

Here’s the CREATE TABLE for notes:

CREATE TABLE `notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_public` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_archived` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `notes_type_id_foreign` (`type_id`),
  KEY `member_id` (`member_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `_notes_type_id_foreign` FOREIGN KEY (`type_id`) REFERENCES `note_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21374344 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Here’s the CREATE TABLE for note_metadata:

CREATE TABLE `note_metadata` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `note_id` bigint(20) unsigned NOT NULL,
  `meta_key_id` int(10) unsigned NOT NULL,
  `meta_value` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `note_metadata_meta_key_id_foreign` (`meta_key_id`),
  KEY `_note_metadata_note_id_foreign` (`note_id`),
  CONSTRAINT `_note_metadata_note_id_foreign` FOREIGN KEY (`note_id`) REFERENCES `notes` (`id`),
  CONSTRAINT `note_metadata_meta_key_id_foreign` FOREIGN KEY (`meta_key_id`) REFERENCES `note_meta_keys` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4098655 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Any idea how I can make the latter query – the one with the ORDER BY – as fast as the former? Do I need to add a new index or some such?

Tags:

3

Answers


  1. 0

    Consider adding the (note_id,meta_key_id,meta_value) index and drop _note_metadata_note_id_foreign index .

    Use,

    SET autocommit=0;
LOCK TABLES note_metadata WRITE;
alter table `note_metadata` 
add index note_mkey_mval(note_id,meta_key_id,meta_value),
drop index `_note_metadata_note_id_foreign`;
COMMIT;
UNLOCK TABLES;

    Multiple-Column Indexes

    The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables

    Login or Signup to reply.
  2. 0

    Because meta_value is defined as a text column, when defining a key you need to specifiy the prefix length. You should add the following composite index:

    ALTER TABLE note_metadata
    ADD INDEX (meta_key_id, meta_value(20), note_id);

    You should change the ORDER BY clause to nm.note_id, instead of n.id.

    Here’s a db<>fiddle.

    Login or Signup to reply.
  3. 0

    Creating an index might help.

     CREATE INDEX notes_id_idx ON notes(id DESC);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search