1

Here i have table with indexes and more than 700000 entries.

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `cat_id` int(11) NOT NULL,
  `user_id` char(255) COLLATE utf8_unicode_ci NOT NULL,
  `created` int(11) NOT NULL,
  `updated` int(11) NOT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `primary` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_unique` (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `created_index` (`created`),
  KEY `cat_id_index` (`cat_id`),
  KEY `updated_index` (`updated`),
  KEY `visible_index` (`visible`),
  KEY `catid_visible_index` (`cat_id`,`visible`),
  KEY `primary_index` (`primary`),
  KEY `topics_forumid_visible_sticky` (`cat_id`,`visible`,`primary`),
  FULLTEXT KEY `topics_title_fulltext` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need to do 2 ORDER BY statement. If i use any order by - query runs too long

 SELECT 
    `t1`.*
FROM
    `t1`
WHERE
    `t1`.`cat_id` = 24
        AND `t1`.`visible` = 1
ORDER BY `t1`.`primary` DESC , `t1`.`updated` DESC
LIMIT 20

EXPLAIN ordered gives Extra 'Using index condition; Using where; Using filesort' Key 'catid_index'

EXPLAIN unordered gives Key 'catid_index,visible_index' Extra 'Using intersect(catid_index,visible_index); Using where'

  • Can you add EXPLAINs for both ordered and unordered versions to your question please? – jkavalik May 30 '16 at 7:34
  • 2
    The "best" index for your query seems to be (cat_id, visible, primary, updated). – jkavalik May 30 '16 at 7:36
1

The "best" index for this query seems to be (cat_id, visible, primary, updated). Thanks to jkavalik

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.