Mysql doesn't use primary index

redrain · April 8, 2009, 8:00am

Hi,
I have a query which appears in the slow logs.

The query is a simple order by id query:
SELECT news_id,news_title FROM news ORDER BY news_id DESC LIMIT 25

This is a MyISAM table and news_id is primary key for the table.

How can i optimise this query?

vgatto · April 8, 2009, 3:13pm

According to your explain, MySQL is using an index scan to evaluate your query. However, if you have --log-queries-not-using-indexes enabled, queries which use index scans will be written to the slow query log regardless of their execution time. Have you tried running this query from the command line client? I’m guessing it takes under a tenth of second to execute.

redrain · April 8, 2009, 3:24pm

Yes, you’re right.

I’m trying to find out what causing mysql to use 190% CPU sometimes. So I’ve enabled “log-queries-not-using-indexes”.

januzi · April 8, 2009, 3:34pm

This is the only one query in the query log ?

redrain · April 8, 2009, 3:53pm

No. But this is the most used/logged query.

And this ones appears sometimes:
SELECT news_id,news_title,news_hit FROM news ORDER BY news_date DESC LIMIT 0, 25;

SELECT news_id,news_title,news_position,news_hit,news_cat,news_date FROM news WHERE DATE(news_date) = CURDATE() ORDER BY
news_date ASC LIMIT 0, 50;

SELECT news_id,news_title,news_summary,news_image_small FROM news WHERE news_cat=‘7’ and news_position=‘7’ ORDER BY news_date DESC LIMIT 1;

SELECT news_id,news_title,news_image,news_summary FROM news WHERE (news_position=‘2’ or news_position=‘6’) and news_cat=‘1’ ORDER BY news_date DESC LIMIT 12;

SELECT * FROM comments WHERE com_status=‘0’ ORDER BY com_date;

I’m working on each query. But I’m not sure if slow queries does this.

This is a heavy load news site. MySQL is the only service on the server. (2.4 GHz Xeon CPU, 2GB RAM)

I can’t give any slow query right now because there isn’t. Slow queries only appears during busy hours. And MySQL CPU usage stays between 100-190%.

januzi · April 8, 2009, 4:20pm

explain SELECT news_id,news_title,news_position,news_hit,news_cat,news_date FROM news WHERE DATE(news_date) = CURDATE() ORDER BY
news_date ASC LIMIT 0, 50;

explain SELECT news_id,news_title,news_summary,news_image_small FROM news WHERE news_cat=‘7’ and news_position=‘7’ ORDER BY news_date DESC LIMIT 1;

explain SELECT news_id,news_title,news_image,news_summary FROM news WHERE (news_position=‘2’ or news_position=‘6’) and news_cat=‘1’ ORDER BY news_date DESC LIMIT 12;

I think that query with DATE(news_date) is the worst. Explain should show fullscan.

redrain · April 8, 2009, 4:37pm

Yes. I mean the query with CURDATE() function does full scan. But that query is only used on a few pages rarely visited.

januzi · April 8, 2009, 5:02pm

Try
explain SELECT news_id,news_title,news_image,news_summary FROM news WHERE (news_position=‘2’ and news_cat = ‘1’ ) or ( news_position=‘6’ and news_cat=‘1’ ) ORDER BY news_date DESC LIMIT 12;

So, the most common query is SELECT news_id,news_title,news_hit FROM news ORDER BY news_date DESC LIMIT 0, 25; ?

Edit:
Please, copy news table (structure) into news_tmp.
Add some records (100 should do it) and then check it with:
explain SELECT news_id,news_title,news_summary,news_image_small FROM news_tmp WHERE news_cat=‘7’ and news_position=‘7’ ORDER BY news_date DESC LIMIT 1;
Create index with two columns: news_cat + news_position, optimize table and check it again. (remember to use valid news_cat and news_position)

redrain · April 9, 2009, 3:24am

redrain · April 9, 2009, 3:39am

Now these queries also appears in slow log:
SELECT news_id,news_title,news_date FROM news WHERE news_cat=‘1’ ORDER BY news_date DESC LIMIT 5;

SELECT news_id,news_title,news_image,news_summary FROM news WHERE news_position=‘2’ ORDER BY news_date DESC LIMIT 12;

SELECT news_id,news_title,news_image_small,news_summary FROM news WHERE news_position IN (8,3,7) and news_cat=‘1’ ORDER BY
news_date DESC LIMIT 0, 2;

januzi · April 9, 2009, 5:44am

Ok, add that new index to the news table.

Did You think about splitting news table into two separate tables ? First with 25 rows - the newest data, second with rest of the data.

redrain · April 9, 2009, 6:07am

I’ve added new index, cat_position.

Now there are 5 indexes:
PRIMARY (news_id)
news_position
news_cat
news_date
cat_position (news_cat, news_position)

Yes I did think about splitting news table into two seperate tables. First one for current day and other for rest of the news. But I couldn’t figure out how can I implement new structure to the site. How to move records between two tables? How to build home page and other pages? etc… I’m still looking for an example but couldn’t find one yet.

Thank you so much.

redrain · April 9, 2009, 6:15am

MySQL still use 170-180% CPU sometimes with no slow log. This was the only one:
SELECT news_id,news_title,news_position FROM news WHERE news_cat=‘5’ ORDER BY news_date DESC LIMIT 6;

januzi · April 9, 2009, 6:42am

Could You paste “show status” ? Skip variables with value = 0

redrain · April 9, 2009, 6:52am

show status;
±----------------------------------±-------------+
| Variable_name | Value |
±----------------------------------±-------------+
| Aborted_clients | 2 |
| Aborted_connects | 2 |
| Bytes_received | 1596 |
| Bytes_sent | 22046 |
| Com_change_db | 1 |
| Com_show_databases | 1 |
| Com_show_fields | 27 |
| Com_show_status | 1 |
| Com_show_tables | 1 |
| Compression | OFF |
| Connections | 862868 |
| Created_tmp_files | 46901 |
| Created_tmp_tables | 3 |
| Flush_commands | 1 |
| Handler_read_rnd_next | 32 |
| Handler_write | 161 |
| Key_blocks_unused | 107350 |
| Key_blocks_used | 11411 |
| Key_read_requests | 54226934 |
| Key_reads | 16980 |
| Key_write_requests | 219634 |
| Key_writes | 7345 |
| Last_query_cost | 21555.899000 |
| Max_used_connections | 380 |
| Open_files | 232 |
| Open_tables | 186 |
| Qcache_free_blocks | 494 |
| Qcache_free_memory | 130821696 |
| Qcache_hits | 3378511 |
| Qcache_inserts | 407703 |
| Qcache_not_cached | 100424 |
| Qcache_queries_in_cache | 928 |
| Qcache_total_blocks | 2393 |
| Questions | 6740807 |
| Rpl_status | NULL |
| Slave_running | OFF |
| Ssl_session_cache_mode | NONE |
| Table_locks_immediate | 843738 |
| Table_locks_waited | 35763 |
| Threads_cached | 4 |
| Threads_connected | 8 |
| Threads_created | 13860 |
| Threads_running | 3 |
| Uptime | 55618 |
±----------------------------------±-------------+
251 rows in set (0.00 sec)

januzi · April 9, 2009, 8:11am

Please run:
Show processlist

and paste output of the “top” with maximal cpu usage

redrain · April 9, 2009, 8:35am

I couldn’t catch a busy time, but this shows 113 % CPU usage:

top - 17:02:26 up 58 days, 13:39, 2 users, load average: 0.58, 0.55, 0.62
Tasks: 70 total, 1 running, 69 sleeping, 0 stopped, 0 zombie
Cpu(s): 27.9%us, 28.5%sy, 0.0%ni, 41.6%id, 0.8%wa, 0.2%hi, 1.0%si, 0.0%st
Mem: 2076468k total, 1304408k used, 772060k free, 203136k buffers
Swap: 1951888k total, 0k used, 1951888k free, 843996k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3049 mysql 15 0 409m 113m 4720 S 113 5.6 217:00.85 mysqld
21761 root 15 0 2228 1120 856 R 0 0.1 0:00.19 top
1 root 15 0 1948 644 552 S 0 0.0 0:03.44 init
2 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1
5 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/1
6 root 10 -5 0 0 0 S 0 0.0 0:00.14 events/0
7 root 10 -5 0 0 0 S 0 0.0 0:00.01 events/1
8 root 10 -5 0 0 0 S 0 0.0 0:01.03 khelper
9 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread
13 root 14 -5 0 0 0 S 0 0.0 0:00.10 kblockd/0
14 root 10 -5 0 0 0 S 0 0.0 0:00.28 kblockd/1
15 root 17 -5 0 0 0 S 0 0.0 0:00.00 kacpid
105 root 11 -5 0 0 0 S 0 0.0 0:00.00 kseriod
145 root 18 -5 0 0 0 S 0 0.0 0:00.00 kswapd0
146 root 18 -5 0 0 0 S 0 0.0 0:00.00 aio/0
147 root 19 -5 0 0 0 S 0 0.0 0:00.00 aio/1
292 root 15 0 0 0 0 S 0 0.0 0:00.04 kirqd
617 root 10 -5 0 0 0 S 0 0.0 0:00.00 khubd
780 root 12 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
794 root 11 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_1

show processlist;
±--------±-----±--------------------±-----±--------±-- —±-------------±---------------------------------------- ------------------------------------------------------------ -+
| Id | User | Host | db | Command | Time | State | Info |
±--------±-----±--------------------±-----±--------±-- —±-------------±---------------------------------------- ------------------------------------------------------------ -+
| 1029605 | root | localhost | gg | Query | 0 | NULL | show processlist |
| 1042170 | gg | 212.175.22.32:50384 | gg | Query | 1 | Sending data | SELECT news_id,news_title,news_position,news_hit,news_cat,news_date FROM news WHERE DATE(news_date) |
| 1042171 | gg | 212.175.22.32:50385 | gg | Sleep | 2 | | NULL |
| 1042198 | gg | 212.175.22.32:38002 | gg | Sleep | 1 | | NULL |
| 1042202 | gg | 212.175.22.32:38006 | gg | Query | 1 | Locked | UPDATE news SET news_hit=‘216’ WHERE news_id=‘182212’ |
| 1042204 | gg | 212.175.22.32:38008 | gg | Sleep | 1 | | NULL |
| 1042209 | gg | 212.175.22.32:38013 | gg | Query | 1 | Locked | SELECT news_id,news_position,news_title,news_hit,news_date FROM news WHERE news_date BETWEEN concat( |
| 1042210 | gg | 212.175.22.32:38014 | gg | Sleep | 1 | | NULL |
±--------±-----±--------------------±-----±--------±-- —±-------------±---------------------------------------- ------------------------------------------------------------ -+
8 rows in set (0.00 sec)

januzi · April 9, 2009, 12:40pm

Hmmm … does cpu usage rise with hdd i/o ?
Could You check Your raid status (if there is a raid) ?
Does hdd run in dma mode ?

Could You run “optimize table news” and “analyze table news” ?

redrain · April 9, 2009, 3:52pm

There is no RAID.

I couldn’t find out dma status:

hdparm -i /dev/sda
/dev/sda:
HDIO_GET_IDENTITY failed: Invalid argument

hdparm -I /dev/sda
/dev/sda:
HDIO_DRIVE_CMD(identify) failed: Invalid argument

I’ve runned optimize/analyze many times.

januzi · April 12, 2009, 7:52am

Please check:

explain SELECT news_id,news_title,news_hit FROM news where news_date in ( $xxx ) ORDER BY news_date DESC LIMIT 0, 25;

where $xxx is list of the dates eg: $xxx = “‘2009-04-01’, ‘2009-04-02’, ‘2009-04-03’” ;

There is a limit of the length of the query, but I think that You will be able to put two months in that list. This should limit amount of the rows.

Topic		Replies	Views
Mysql not using index for ORDER BY ? Other MySQL® Questions	2	521	May 13, 2007
Optimizing simple order by query Other MySQL® Questions	1	330	December 25, 2009
INDEX on DATETIME column Other MySQL® Questions	1	13329	March 15, 2007
USE INDEX (PRIMARY) is much faster, even when not looking for anything in that column Percona Server for MySQL 5.7	1	2400	April 24, 2018
Indexing oddity Other MySQL® Questions	4	528	October 20, 2010

Mysql doesn't use primary index

Related topics