indexing - Why my index is not used in a mysql selection -

table definition:

create table `titles` (   `emp_no` int(11) not null,   `title` varchar(50) not null,   `from_date` date not null,   `to_date` date default null,   primary key (`emp_no`,`title`,`from_date`),  ) engine=innodb default charset=utf8 

the query is:

explain select * employees.titles emp_no < '10010' , title='senior engineer'; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows |       | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ |  1 | simple      | titles | range | primary       | primary | 4       | null |   16 | using | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 

my question why first column can use index? know many article/doc give conclusion i'd know detail explanation.

my understanding is, mysql can scan btree index , find collection of keys match emp_no < '10010' , filter them on title='senior engineer', why said from_data column can not use index? (btw think know how b+ tree works).

thanks.

---

below output of explain format=json:

{   "query_block": {     "select_id": 1,     "cost_info": {       "query_cost": "1.41"     },     "table": {       "table_name": "titles",       "access_type": "range",       "possible_keys": [         "primary"       ],       "key": "primary",       "used_key_parts": [         "emp_no"       ],       "key_length": "4",       "rows_examined_per_scan": 1,       "rows_produced_per_join": 1,       "filtered": "100.00",       "cost_info": {         "read_cost": "1.21",         "eval_cost": "0.20",         "prefix_cost": "1.41",         "data_read_per_join": "168"       },       "used_columns": [         "emp_no",         "title",         "from_date",         "to_date"       ],       "attached_condition": "((`employees`.`titles`.`emp_no` < '10010') , (`employees`.`titles`.`title` = 'senior engineer'))"     }   } } 

the issue here table small, when index reduce search cost, using index have cost itself.

so small tables cost of using index bigger read whole table. try include more rows , try again.