Mysql. Нужно ли ставить индексы на поля которые участвуют в ORDER BY?

Question

[Дмитрий Сергеев]

Данные: таблица объявлений

CREATE TABLE IF NOT EXISTS `blurb_topics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `poster` varchar(200) NOT NULL,
  `poster_id` int(10) unsigned NOT NULL DEFAULT '0',
  `subject` varchar(255) NOT NULL, # заголовок 
  `posted` int(11) unsigned NOT NULL DEFAULT '0', # время поста, timestamp
  `forum_id` int(10) unsigned NOT NULL DEFAULT '0', # id подкатегории
  `cat_id` int(11) NOT NULL DEFAULT '0', # id категории
  `sticky` int(11) unsigned NOT NULL DEFAULT '0', # "закрепление" в топе на первом месте, timestamp
   'deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', 
  `fliped` int(11) unsigned NOT NULL, # время "подбросить" объявления, timestamp
  `path` varchar(300) DEFAULT NULL,
  `separated` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `topics_forum_id_idx` (`forum_id`),
  KEY `cat_id` (`cat_id`),
  KEY `poster_id` (`poster_id`),
  KEY `posted` (`posted`,`sticky`,`fliped`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

Нагрузка на mysql возрастает когда, примерно, 3 пользователя сёрфят список объявлений, который получается запросом

SELECT * blurb_topics WHERE deleted=0 ORDER BY (sticky > 0) DESC, fliped DESC, posted DESC LIMIT 0, 50 ');

Про поля: sticky — время «закрепления» объявления в топе, вернее time()+future(); fliped — пользователь может «подбросить» своё объявление что бы не дублировать информацию.


Я делал смешанный индекс `posted`+`sticky`+`fliped`+`deleted`, mysql вообще перестал использовать индекс судя по explain. Сейчас он использует индекс только по `deleted`


Как правильно расставить индексы или может быть перелопатить структуру?


MySQL Server version: 5.5.15-log Source distribution

PHP 5.3.6 with Suhosin-Patch в режиме php-fpm

VPS 1Gb Ram, 2400 МГц

Answer 1

[rakot]

Тут всё дело с условием (sticky > 0), именно оно мешает использовать индекс.
Возможно, имеет смысл сделать sticky enum('no', 'yes') и sticky_timestamp и использовать именно данный sticky.

Comments

[rakot]

Всё разобрался в чём запара. Если не отказаться от (sticky > 0), то индекс не будет использоваться, потому что в данном случае он проверяем условие и ставит временный флаг 0 или 1 и сортирует именно по нему, а не по индексу, поэтому тут и не помогает составной индекс.

Нужно дополнительное поле, чтобы избавиться от данного условия и как сказал , порядок полей в индесе важен и необходимо чтобы он охватывал всё условие ORDER BY. В конкретном случае это будет `sticky`+`fliped`+`posted`, именно в данном порядке.

Answer 2

[dmitryklerik]

Mysql не использует индексы если ORDER BY выполняется по различным колонкам.
Пруф: dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html — подробно все написано

Answer 3

[dkurilenko]

можно создать cron job (что то похожее на Oracle Materialized View), который каждые десять минут будет делать пересчет тех обяхвлений которые в топе и сохранять препросчитанные ид в другую таблицу.

CREATE TABLE `top_topics` (
`blurb_topics_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`blurb_topics_id`),
}

Затем просто соединять с оригинальной таблицей.

Answer 4

[bekbulatov]

Использование индекса в mysql зависит также от порядка следования столбцов в индексе. То есть `posted`+`sticky`+`fliped`+`deleted` использоваться не будет, а `deleted`+`sticky`+`fliped`+`posted` может и будет (Как MySQL оптимизирует ORDER BY)