d p d p - 5 months ago 17
MySQL Question

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

I am using MySQL database. I have a table that consist up to 75K rows of data. I am using simple query to fetch data:

select * from mytable


It works fine, shows 75k rows in few seconds. I wanted to fetch some of these data I had used
limit 10000
. It got stuck every time. I need to optimize MySQL query for 10k records.
I am using query like this:

select * from mytable limit 10000


Give me some solution how to execute my query fast.

my database structure is like 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 ;

d p d p
Answer

With this solution I can fetch larger data in few seconds:

SELECT    l.id,l.col1,l.col2,l.col3,l.col3,l.col4,l.col5,l.col6,l.col7
FROM      (
           SELECT   id
           FROM     mytable
           WHERE    removed='0'
           ORDER BY id
           LIMIT 10000
          ) o
JOIN      mytable ON l.id = o.id
ORDER BY  l.id
Comments