MySQL query takes long to execute if using ORDER BY String Column -

MySQL query takes long to execute if using ORDER BY String Column -

so query on table contains 4 million records executes instant if dont utilize order by. want give clients way sort results name field , show lastly 100 of filtered result. add together order name takes 100 seconds execute.

my table construction similar this:

create table test( id int primary key auto_increment, name varchar(100), statusid int, key (statusid), <-- index on statusid key (statusid, name) <-- index on statusid, name key(name) <-- index on name );

my query like:

explain select id, statusid, name test statusid = 113 order name desc limit 0, 100

above explain when order name gives result:

statusid_2 composite index of statausid, name

now if alter order name desc order id this:

how can create examines 100 rows when using order name?

you can seek 1 thing, seek letters in 100 rows expected in result like

select * test *** joins filter info or more columns other tables statusid = 12 , name regexp '^[a-h]' order name desc limit 0, 100

moreover using index of import on name (which applied) – in case index range scan started , query execution stopped shortly required amount of rows generated.

so can't utilize id nil won't scan when has reached limit, thing can seek remove letters not possible in expected result , trying regexp

mysql sql-order-by query-optimization