Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql index configuration

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.

like image 552
intacto Avatar asked Oct 23 '09 11:10

intacto


People also ask

How do I create an index in MySQL?

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)

How does MySQL choose index?

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.

Which column should be indexed in MySQL?

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).


1 Answers

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
like image 137
Quassnoi Avatar answered Sep 30 '22 02:09

Quassnoi