I have a mySQL DB of ~1 milllion entries.
I run the query:
SELECT a.id as aid, a.title as atitle, a.slug, summary,
a.link as alink, author, published, image, a.cat as acat,
a.rss as arss, a.site as asite
FROM articles a
ORDER BY published DESC
LIMIT 616150, 50;
It takes about 5 minutes or more ot load.
My TABLE and INDEXes:
CREATE TABLE IF NOT EXISTS `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL,
`summary` text NOT NULL,
`link` text NOT NULL,
`author` varchar(255) NOT NULL,
`published` datetime NOT NULL,
`image` text NOT NULL,
`cat` int(11) NOT NULL,
`rss` int(11) NOT NULL,
`site` int(11) NOT NULL,
`bitly` varchar(255) NOT NULL,
`checked` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`),
KEY `cat` (`cat`),
KEY `published` (`published`),
KEY `site` (`site`),
KEY `rss` (`rss`),
KEY `checked` (`checked`),
KEY `id_publ_index` (`id`,`published`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1230234;
What explain says:
mysql> EXPLAIN EXTENDED SELECT a.id as aid, a.title as atitle, a.slug, summary, a.link as alink, author, published, image, a.cat as acat, a.rss as arss, a.site as asite FROM articles a ORDER BY published DESC LIMIT 616150, 50;
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+-------+
| 1 | SIMPLE | a | index | NULL | published | 8 | NULL | 616200 | 152.94 | |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.46 sec)
Any tips of how to optimize this query? Why mySQL needs to read all 616200 rows and not just the 50 that were asked?
Thank you for your time.
No comments:
Post a Comment