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.
Generally, we create an index at the time of table creation in the database. The following statement creates a table with an index that contains two columns col2 and col3. If we want to add index in table, we will use the CREATE INDEX statement as follows: mysql> CREATE INDEX [index_name] ON [table_name] (column names)
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index). If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
In some cases, it also makes sense to create a separate index that contains the ORDER BY clause's columns, as MySQL sometimes chooses to use it. Please note though that for this to happen, the index should contain all columns from the ORDER BY clause and they should all be specified with the same order (ASC / DESC).
Update:
See this article in my blog for the more detailed analysis of the problem:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With