how dose mysql(InnoDB) chose a proper index? -

there key idx_from_to_created_ts(from,to,created_ts). , query sql this:

select * trade_orders = 'hk' , to='bj' , created_ts>1504972800; 

the query conditions match key idx_from_to_created_ts perfectly. in fact, key seems not chosen. explain:

in relational database using row-structured storage, secondary index stored in separate storage area away "base table data". when create index, base table traversed fetch columns indexed, inserted stored index structure - b-tree - , saved persistent storage persistent tables.

index entries "rows", containing indexed column(s) , sort of offset base table data. when index used fetch row, index walked until finds row(s) of interest, , base table looked fetch actual row data.

when row inserted, corresponding row written index, , when row deleted, index row taken out.

note why indexes take space, , why having lot of indexes slow down write operations on base table: indexes have kept in sync table, every insert or delete underlying table, there activity in indexes, , updates underlying table - if updated column isn't in index - may trigger index activity base table data storage managers store row offsets may need updated.

the exact behavior depends on storage engine implementation.

some storage engines implement "primary indexes", on primary key. commonly used storage engine of type mysql innodb - in case, base table data stored in b-tree structure, , secondary indexes store primary key each row. oracle's "index organized tables" implemented way.

the big advantage of structure lookups on primary key extremely fast 1 structure must visited pull rows opposed 2 traditional "heap + secondary index" storage.

there many interesting variations on indexes: "incomplete" indexes, meaning indexes loaded if property met (these useful if have enormous table search on specific column values), "function-based" indexes, index values computed on function, etc. postgresql has lot of these.

there many tools can help, using sqldbm find effective. maybe should check out.