mysql - Fastest result when checking date range -

user select date e.g. 06-mar-2017 , need retrieve hundred thousand of records date earlier 06-mar-2017 (but vary depends on user selection).

from above case, using queryselect col table_a date_format(mydate,'%y%m%d') < '20170306' feel record kind of slow. there faster or fastest way date results this?

with 100,000 records read, dbms may decide read table record record (full table scan) , there wouldn't do.

if on other hand table contains billions of records, 100,000 small part, dbms may decide use index instead.

in way should @ least give dbms opportunity select via index. means: create index first (if such doesn't exist yet).

you can create index on date column alone:

create index idx on table_a (mydate); 

or provide covering index contains other columns used in query, too:

create index idx on table_a (mydate, col); 

then write query such date column accessed directly. have no index on date_format(mydate,'%y%m%d'), above indexes don't original query. you'd need query looks date itself:

select col table_a mydate < date '2017-03-06'; 

whether dbms uses index or not still dbms. try use fastest approach, can still full table scan.