Simple mySQL query does not use a index

Question

I have very strange problem with mySQL and simple query with simple index. I am trying to use this query:

SELECT * FROM `counter_links` WHERE `link_id`=1544;

as you can see there is also index named link_id:

mysql> show indexes from counter_links;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| Table         | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  | Index_comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| counter_links |          1 | link_id     |            1 | link_id     | A         |        NULL |     NULL | NULL   |      | BTREE      | disabled |               |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
1 row in set (0.12 sec)

But look what EXPLAIN returned:

mysql> explain SELECT * FROM `counter_links` WHERE `link_id`=1544;
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | counter_links | ALL  | NULL          | NULL | NULL    | NULL | 22103687 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.03 sec)

Why mySQL don't use index there? I see that the same is working good on smaller version of the table, but I am not able to recognize where the problem is. You do?

It is more stranger that it seems, because lower IDs sometimes works with the index.

Thanks you for all comments in advance!

Cheers!

Jakub

Can you show the CREATE TABLE of counter_links maybe?

Kerrek SB
– Kerrek SB

2011-07-07 15:37:27 +00:00
Commented Jul 7, 2011 at 15:37 — Kerrek SB
– Kerrek SB, Commented Jul 7, 2011 at 15:37

Jaydee · Accepted Answer · 2011-07-07 15:38:45Z

3

The comment field for th eindex says that the index is disabled, not sure why.

ALTER TABLE ... ENABLE KEYS may work

answered Jul 7, 2011 at 15:38

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Simple mySQL query does not use a index

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related