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