I have a table for logging data into, example
TABLE IF NOT EXISTS `message_log` ( `id` int(8) NOT NULL AUTO_INCREMENT, `user_id` int(8) unsigned NOT NULL, `channel` tinyint(8) unsigned NOT NULL DEFAULT '0', `type` tinyint(4) NOT NULL DEFAULT '0', `message` varchar(255) NOT NULL, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `confirmed` datetime DEFAULT NULL, PRIMARY KEY (`id`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (user_id) PARTITIONS 11 */ AUTO_INCREMENT=4569
(Note - I know it should be InnoDB, but this isn't the question)
Regarding searching, it can be searched on user_id, user_id and channel, or user_id and channel and type. user_id could be any number. channel is 1-20. type is 1-3.
Now user_id seems an obvious candidate to index, but would I need to index channel and type being that they are a relatively small set of possibilities?