MySQL index doesn't work

Question

I got a weird problem of MySQL index. I have a table views_video:

CREATE TABLE `views_video` (
  `video_id` smallint(5) unsigned NOT NULL,
  `record_date` date NOT NULL,
  `region` char(2) NOT NULL DEFAULT '',
  `views` mediumint(8) unsigned NOT NULL
  PRIMARY KEY (`video_id`,`record_date`,`region`),
  KEY `video_id` (`video_id`)
)

The table contains 3.4 million records.

I run the EXPLAIN on this query:

SELECT video_id, views FROM views_video where video_id <= 156

I got:

+----+-------------+-------------+-------+------------------+----------+---------+------+--------+-------------+
| id | select_type | table       | type  | possible_keys    | key      | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+-------+------------------+----------+---------+------+--------+-------------+
|  1 | SIMPLE      | views_video | range | PRIMARY,video_id | video_id | 2       | NULL | 587984 | Using where |
+----+-------------+-------------+-------+------------------+----------+---------+------+--------+-------------+

But when I run the EXPLAIN on this query:

SELECT video_id, views FROM views_video where video_id <= 157

I got:

+----+-------------+-------------+------+------------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys    | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | views_video | ALL  | PRIMARY,video_id | NULL | NULL    | NULL | 3412892 | Using where |
+----+-------------+-------------+------+------------------+------+---------+------+---------+-------------+

video_id is from 1 to 1034. There is nothing special between 156 and 157.

What happens here?

* update *

I have added more data into the database. Now video_id is from 1 to 1064. And the table now has 3.8M records. And the difference become 114 and 115.

Ok, then is there any significat speed difference between this two queries ? If not, then cause can be something on lower level, don't bother — SergeS
– SergeS, Commented Jul 12, 2012 at 21:14

Poodlehat · Accepted Answer · 2012-07-12 21:42:38Z

2

I'm guessing that with 3.4 million records, and only 1064 possible entries for your key, your selectivity is very low. (In other words, there are many duplicates, which makes it far less useful as a key.) The optimizer is taking its best guess if it is more efficient to use the key or not. You've found a threshold for that decision.

answered Jul 12, 2012 at 21:42

Poodlehat

3601 gold badge2 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RolandoMySQLDBA · Accepted Answer · 2012-07-12 21:27:54Z

2

It might be the key population

Run these

SELECT (COUNT(1)/20) INTO @FivePctOfData FROM views_video;
SELECT COUNT(1) videpidcount,video_id FROM FROM views_video
WHERE id <= 157 GROUP BY video_id;

The query optimizer proabably took a vacation when one one of the key hit the 5% threshold.

You said there are 3.4 million rows. 5% would be 170,000. Perhaps this number was exceeded at some point in the query optimizer's life cycle on your query.

answered Jul 12, 2012 at 21:27

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

Comments

J. Miller · Accepted Answer · 2012-07-12 21:33:05Z

2

If you've added/deleted substantial data since creating the table, it's worthwhile to try ANALYZE TABLE on it. It frequently solves a lot of phantom indexing issues, and it's very fast even on large tables.

Update: Also, the unique index values are very low compared to the number of rows in the table. MySQL won't use indexes when a single index value points to too many rows. Try constraining the query further with another column that's part of the primary key.

edited Jul 12, 2012 at 21:33

answered Jul 12, 2012 at 21:15

J. Miller

7574 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL index doesn't work

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related