Question 112844 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL index does not work if I select all fields

I have a simple table like this,

CREATE TABLE `domain` (
  `id` varchar(191) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  `task_id` bigint(20) DEFAULT NULL,
  `name` varchar(512) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `idx_domain_time` (`time`),
  KEY `idx_domain_task_id` (`task_id`),
  FULLTEXT KEY `idx_domain_name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

And indexed like this:

mysql> show index from domain;
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table  | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| domain |          0 | PRIMARY                |            1 | id          | A         |     2036092 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| domain |          1 | idx_domain_name        |            1 | name        | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         
+--------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Index is used when I select only the id field:

mysql> explain SELECT id FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key                | key_len | ref   | rows    | Extra       |
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
|    1 | SIMPLE      | domain | ref  | idx_domain_task_id | idx_domain_task_id | 9       | const | 1018046 | Using index |
+------+-------------+--------+------+--------------------+--------------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

When I select all fields, it does not work:

mysql> explain SELECT * FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | domain | ALL  | idx_domain_task_id | NULL | NULL    | NULL | 2036092 | Using where |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT id, name FROM `domain` WHERE task_id = '3';
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
| id   | select_type | table  | type | possible_keys      | key  | key_len | ref  | rows    | Extra       |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | domain | ALL  | idx_domain_task_id | NULL | NULL    | NULL | 2036092 | Using where |
+------+-------------+--------+------+--------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

What’s wrong?

Tags:

2

Answers


  1. 0

    Indexes other than the Primary Key work by storing data for the indexed field(s) in index order, along with the primary key.

    So when you SELECT the primary key by the indexed field, there is enough information in the index to completely satisfy the query. When you add other fields, there’s no longer enough information in the index. That doesn’t mean the database won’t use the index, but now it’s no longer as much of a slam dunk, and it comes down more to table statistics.

    Login or Signup to reply.
  2. 0

    MySql optimizer will try to achieve the best performance so it may ignore an index. You can force optimizer to use the index you want if you are sure that will give you better performance. You can use :

    SELECT * FROM `domain` USE INDEX (idx_domain_task_id)  WHERE task_id = '3';

    For more details please see this page Index Hints .

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search