mysql multi column index not working (as expected)?

Question

I have a table like this

CREATE TABLE IF NOT EXISTS `tbl_folder` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_userid` int(11) NOT NULL,
  `name` varchar(63) NOT NULL,
  `description` text NOT NULL,
  `visibility` tinyint(4) NOT NULL DEFAULT '2',
  `num_items` int(11) NOT NULL DEFAULT '0',
  `num_subscribers` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `owner_userid` (`owner_userid`),
  KEY `vis_sub_item` (`visibility`,`num_subscribers`,`num_items`)
) ENGINE=InnoDB

since I have an index on visibility, num_subscribers and num_items, I expect that only the first 15 rows only have to be looked at, instead, EXPLAIN says 55856 rows. Any idea? Thanks

EXPLAIN SELECT t.id, name, description, owner_userid, num_items, num_subscribers
FROM  `tbl_folder`  `t` 
WHERE visibility =2
ORDER BY  `t`.`num_subscribers` DESC ,  `t`.`num_items` DESC 
LIMIT 15

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref vis_sub_item vis_sub_item 1 const 55856 Using where

Answer 1

Your 3 field index looks good and the EXPLAIN is promising.

Although it says "55856 rows", that is just an estimate provided by EXPLAIN.

Since key_len =1, you know it's using the first byte of your compound index as an equality/reference.

Since there is no filesort mentioned in your Extra field, you know that the ORDER BY/sorting is being handled by the index.

If you check your handler_% session stats, you'll have a better idea of how many rows are actually being read.

Side Thoughts:

Since you know you're ultimately going to hit disk to retrieve your rows, if 99% of your data has visibility=2 (just speculating), you'd likely get as equally good/fast results with a compound index just on num_subscribers & num_items. Or arguably as good/fast if you has a single index on num_subscribers, depending on it's cardinality/uniqueness.