Mysql index configuration

mysql, performance, sql

Solution

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

Problem

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.

Original source