mysql. Whether it is necessary to put indexes on fields which participate in ORDER BY?

Question

MySQL

Data: declaration table

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 ;

The load on mysql increases when about 3 users surf the list of ads, which is obtained by the query

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

About the fields: sticky - the time of "fixing" the ad in the top, or rather time() + future(); fliped - the user can "flip" his ad so as not to duplicate information.
I did a mixed index `posted`+`sticky`+`fliped`+`deleted`, mysql stopped using the index altogether, judging by explain. Now it uses an index only on `deleted`
How to arrange indexes correctly or maybe shovel the structure?
MySQL Server version: 5.5.15-log Source distribution
PHP 5.3.6 with Suhosin-Patch in php-fpm mode
VPS 1Gb Ram, 2400 MHz

Answer 1

It's all about the condition (sticky > 0), it is this condition that prevents the use of the index.
It might make sense to sticky enum('no', 'yes') and sticky_timestamp and use that sticky.

Answer 2

Mysql does not use indexes if ORDER BY is performed on different columns.
Proof: dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html - everything is written in detail

Answer 3

you can create a cron job (something similar to Oracle Materialized View), which every ten minutes will recalculate those advertisements that are in the top and save the precalculated IDs to another table.
CREATE TABLE `top_topics` (
`blurb_topics_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`blurb_topics_id`),
}
Then just join with the original table.

Answer 4

The use of an index in mysql also depends on the order of the columns in the index. That is, `posted`+`sticky`+`fliped`+`deleted` will not be used, but `deleted`+`sticky`+`fliped`+`posted` can and will be ( How MySQL optimizes ORDER BY )