how the sql works by in a sql about using index

how can this sql use index and how can this sql not use index.

CREATE TABLE `testtable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_abd` (`a`,`b`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

explain select * from testtable where a > 1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testtable | NULL       | ALL  | idx_abd       | NULL | NULL    | NULL |   10 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+


explain select * from testtable where a < 1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | testtable | NULL       | range | idx_abd       | idx_abd | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

why the first one can’t use index but the second use index. how the index works inside?

Answer

In first case, MySQL optimizer (based on statistics) decided that it is better to do a Full Table Scan, instead of first doing Index Lookups, and then do a Data Lookup.

In the first query of yours, the condition used (a > 1) is effectively needing to access 10 out of 11 rows. Always remember that, MySQL does Cost based optimization (tries to minimize the cost). The process is basically:

1. Assign a cost to each operation.
2. Evaluate how many operations each possible plan would take.
3. Sum up the total.
4. Choose the plan with the lowest overall cost.

Now, default MySQL cost for io_block_read_cost is 1. In the first query, you are going to roughly have two times the I/O block reads (first for index lookups and then Data lookups). So, the cost would come out roughly as 20, in case MySQL decides to use the index. Instead, if it does the Table Scan directly, the cost would be roughly 11 (Data lookup on all the rows). That is why, it decided to use Table Scan instead of Range based Index Scan.

If you want to get details about the Cost breakup, please run each of this queries by appending EXPLAIN format=JSON to them and executing them, like below:

EXPLAIN format=JSON select * from testtable where a > 1;

You can also see how Optimizer compared various plans before locking into a particular strategy. To do this, execute the queries below:

/* Turn tracing on (it's off by default): */
SET optimizer_trace="enabled=on";
SELECT * FROM testtable WHERE a > 1; /* your query here */
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
/* possibly more queries...
 When done with tracing, disable it: */
SET optimizer_trace="enabled=off";

Check more details at MySQL documentation: https://dev.mysql.com/doc/internals/en/optimizer-tracing.html