Is index dependent on selected columns?

Question

I am executing most of the queries based on the time. So i created index for the created time. But , The index only works , If I select the indexed columns only. Is mysql index is dependant the selected columns?.

My Assumption On Index

I thought index is like a telephone dictionary index page. Ex: If i want to find "Mark" . Index page shows which page character "M" starts in the directory. I think as same as the mysql works.

Table

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| ID           | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(100) | YES  |     | NULL    |                |
| OPERATION    | varchar(100) | YES  |     | NULL    |                |
| PID         | int(11)      | YES  |     | NULL    |                |
| CREATED_TIME | bigint(20)   | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Indexes On the table.

    +-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| IndexTest |          0 | PRIMARY  |            1 | ID           | A         |       10261 |     NULL | NULL   |      | BTREE      |         |               |
| IndexTest |          1 | t_dx     |            1 | CREATED_TIME | A         |         410 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Queries Using Indexes:

   explain select * from IndexTest where ID < 5;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | IndexTest | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+



  explain select CREATED_TIME from IndexTest where CREATED_TIME > UNIX_TIMESTAMP(CURRENT_DATE())*1000;
    +----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | IndexTest | range | t_dx          | t_dx | 9       | NULL | 5248 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------+---------+------+------+--------------------------+

Queries Not using Indexes

    explain select count(distinct(PID)) from IndexTest where CREATED_TIME > UNIX_TIMESTAMP(CURRENT_DATE())*1000;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | IndexTest | ALL  | t_dx          | NULL | NULL    | NULL | 10261 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+


    explain select PID from IndexTest where CREATED_TIME > UNIX_TIMESTAMP(CURRENT_DATE())*1000;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | IndexTest | ALL  | t_dx          | NULL | NULL    | NULL | 10261 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

Answer 1

Short answer: No.

Whether indexes are used depends on the expresion in your WHERE clause, JOINs etc, but not on the columns you select.

But no rule without an exception (or actually a long list of those):

Long answer: Usually not

There are a number of factors used by the MySQL Optimizer in order to determine whether it should use an index.

The optimizer may decide to ignore an index if...

• another (otherwise non-optimal) saves it from accessing the table data at all
• it fails to understand that an expression is a constant
• its estimates suggest it will return the full table anyway
• if its use will cause the creation of a temporary file
• ... and tons of other reasons, some of which seem not to be documented anywhere

Sometimes the choices made by said optimizer are... erm... lets call them sub-optimal. Now what do you do in those cases?

• You can help the optimizer by doing an OPTIMIZE TABLE and/or ANALYZE TABLE. That is easy to do, and sometimes helps.
• You can make it use a certain index with the USE INDEX(indexname) or FORCE INDEX(indexname) syntax
• You can make it ignore a certain index with the IGNORE INDEX(indexname) syntax

More details on Index Hints, Optimize Table and Analyze Table on the MySQL documentation website.