Mysql index configuration

Question

Hello, I have a table with 450000 row full of news. The table schema is like this:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL auto_increment,
  `cat_id` int(11) NOT NULL,
  `title` tinytext NOT NULL,
  `content` text NOT NULL,
  `date` int(11) NOT NULL,
  `readcount` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `cat_id_2` (`cat_id`,`id`),
  KEY `cat_id_date` (`cat_id`,`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin5 AUTO_INCREMENT=462679 ;

When i run a sql command like below to take some news for a page "x" of the category page it takes more than 15 seconds if x is over 100:

select * news where cat_id='4' order by id desc limit 150000,10;

explain shows that its using "where" and the index "cat_id_2"

While writing this question i also checked a more simple sql query like this and it also took near to a minute:

select * from haberler order by id desc limit 40000,10;

if the sql is like the following one it takes just a few milliseconds:

select * from haberler order by id desc limit 20,10;

My my.cnf configuration is like this:

skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=256M
query_cache_type=1
max_connections=30
interactive_timeout=600000
#wait_timeout=5
#connect_timeout=5
thread_cache_size=384
key_buffer=256M
join_buffer=4M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=64M
read_buffer_size=16M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=128M
long_query_time         = 1
log_slow_queries        = /var/log/mysql/mysql-slow.log
max_heap_table_size=512M

The website is running on a core2duo with 2GB of RAM. I think that the problem may be caused by the sort_buffer_size but i'm not sure. thanks in advance.

Answer 1

Update:

See this article in my blog for the more detailed analysis of the problem:

• MySQL ORDER BY / LIMIT performance: late row lookups

---

When you issue something like LIMIT 150000, 10, it means that MySQL should traverse these 150,000 records and find the next 10.

Traversing the index is slow in MySQL.

Also, MySQL is not capable of doing late row lookups.

Theoretically, if you do ORDER BY id LIMIT 100000, 10, it is enough to use the index to find the values from 100000 to 100010, then look up only 10 rows that satisfy that index and return them.

All major systems except MySQL are aware of it and look the rows up only if the values are really to be returned.

MySQL, however, looks up every row.

Try to rewrite your query as this:

SELECT  news.*
FROM    (
        SELECT  id
        FROM    news
        WHERE   cat_id='4'
        ORDER BY
                id DESC
        LIMIT 150000, 10
        ) o
JOIN    news
ON      news.id = o.id