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

Wrap your SQL in the "code block" tag so we can read it easier please. It helps it stand out and makes it easier to read. — Ben, Dec 08 '10 at 19:37

Riedsio · Accepted Answer · 2010-12-08T21:32:56.787

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.

score 0 · Answer 2 · answered Dec 08 '10 at 20:27

I don't think that EXPLAIN looks at the OFFSET or LIMIT clause. EXPLAIN is supposed to indicate how the query would be executed, what keys it used, how tables are joined, etc. The LIMIT clause is sort of like a post query modifier...now that we know what we want, only give em the first so many. So, the rows field contains the number of possible rows that exist in the query. From there, OFFSET and LIMIT would select the specific ones you want.

I'm figuring that if you executed your SELECT without EXPLAIN, you'd get the number of records you wanted.

score 0 · Answer 3 · answered Dec 08 '10 at 20:48

yes, the problem is that your index is not correct. I mean you indexed all 3 fields and your select query only checks for one. In MySQL indexing 2 rows separately is different from indexing 3 rows together.

Try

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_index` (`visibility`),
  KEY `vis_sub_item` (`num_subscribers`,`num_items`)
) ENGINE=InnoDB

mysql multi column index not working (as expected)?

3 Answers3