InnoDb Select Performance

[Luis Cavazos]

Hi All,
I have a Table with over 2.5 millions of records,

when I do a select * limit 10 records it returns the data quikly,
ie: select * from mkpcte limit 1000 ; will respond in about 0.075 Sec.


if I do a Select using order by Will take 4.5 seconds just to return 2 records
ie: select * from mkpcte order by idreg desc , idano desc limit 2 ; will respond in about 4.175 Sec.

The primary key is idreg , idano, Also I have a Unique Index ( idano desc , idreg desc ) to match the order by.

the Explain says only tye SIMPLE, no PK no Index, and Using File sort


Running Mysql 5.6 64bit on 8 Core Server mem_pool_size 5gb.


Any Idea/Sugesstion.