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_i开发者_JAVA技巧tems, 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
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.
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.
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
精彩评论