Mysql – Using indexes in ORDER BY MySQL

indexMySQL

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'

Best Answer

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