Tuesday, February 27, 2018

optimization - How to index 1 million rows mySQL table for simple query

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

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...