limit in mysql with 10K record taking too much time to execute(fail every time) -

limit in mysql with 10K record taking too much time to execute(fail every time) -

i using mysql database. have table consist 75k rows of data. using simple query fetch data:

select * mytable

it works fine, shows 75k rows in few seconds. wanted fetch of these info had used limit 10000. got stuck every time. need optimize mysql query 10k records. using query this:

select * mytable limit 10000

give me solution how execute query fast.

my database construction that:

create table if not exists `mytable` ( `id` bigint(20) not null auto_increment, `col1` varchar(255) collate utf8_unicode_ci not null, `col2` varchar(255) collate utf8_unicode_ci not null, `col3` varchar(255) collate utf8_unicode_ci not null, `col4` varchar(255) collate utf8_unicode_ci not null default '', `col5` int(11) not null, `col6` timestamp not null default current_timestamp, `col7` varchar(255) collate utf8_unicode_ci not null, `col8` int(11) not null, `col9` int(11) not null, `col11` int(11) not null, `col12` timestamp not null default '0000-00-00 00:00:00', `col13` timestamp not null default '0000-00-00 00:00:00', `col15` varchar(255) collate utf8_unicode_ci not null, `col16` enum('0','1') collate utf8_unicode_ci not null default '0', `col17` int(11) not null, `col18` varchar(255) collate utf8_unicode_ci not null, `col19` enum('0','1') collate utf8_unicode_ci not null, primary key (`id`), key `id` (`id`,`col2`,`col3`,`col4`,`col5`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci auto_increment=75530 ;

hy all,

i found solution 100% working me can fetch larger info in few seconds

select l.id,l.col1,l.col2,l.col3,l.col3,l.col4,l.col5,l.col6,l.col7 ( select id mytable removed='0' order id limit 10000 ) o bring together mytable on l.id = o.id order l.id

this query working cool

mysql