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.

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

This is the only one query in the query log ?

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

Chances are you just need to add some indexes for these other queries. What does the output of SHOW CREATE TABLE news look like?

CREATE TABLE `news` (
`news_id` int(11) NOT NULL auto_increment,
`news_cat` tinyint(4) NOT NULL,
`news_title` varchar(255) collate utf8_turkish_ci NOT NULL default 'Title',
`news_summary` varchar(255) collate utf8_turkish_ci NOT NULL default '',
`news_text` text collate utf8_turkish_ci NOT NULL,
`news_image` varchar(50) collate utf8_turkish_ci NOT NULL default '',
`news_image_small` varchar(50) collate utf8_turkish_ci NOT NULL default '',
`news_position` tinyint(4) NOT NULL default '1',
`news_date` datetime NOT NULL default '0000-00-00 00:00:00',
`news_hit` int(11) NOT NULL default '0',
PRIMARY KEY (`news_id`),
KEY `news_position` (`news_position`),
KEY `news_cat` (`news_cat`),
KEY `news_date` (`news_date`)
) ENGINE=MyISAM AUTO_INCREMENT=184755 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci PACK_KEYS=0

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.

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

mysql> 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;
+----+-------------+-------+-------+---------------+-------- ---+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------- ---+---------+------+--------+-------------+
| 1 | SIMPLE | news | index | NULL | news_date | 8 | NULL | 179101 | Using where |
+----+-------------+-------+-------+---------------+-------- ---+---------+------+--------+-------------+
1 row in set (0.00 sec)


mysql> 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;
+----+-------------+-------+-------------+------------------ ------+------------------------+---------+------+------+---- ------------------------------------------------------------ ------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+------------------ ------+------------------------+---------+------+------+---- ------------------------------------------------------------ ------+
| 1 | SIMPLE | news | index_merge | news_position,news_cat | news_cat,news_position | 1,1 | NULL | 651 | Using intersect(news_cat,news_position); Using where; Using filesort |
+----+-------------+-------+-------------+------------------ ------+------------------------+---------+------+------+---- ------------------------------------------------------------ ------+
1 row in set (0.00 sec)


mysql> 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;
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
| 1 | SIMPLE | news | ref | news_position,news_cat | news_cat | 1 | const | 49552 | Using where; Using filesort |
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
1 row in set (0.00 sec)

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)

Before new index:
explain SELECT news_id,news_title,news_summary,news_image_small FROM news_tmp WHERE news_cat='1' and news_position='7' ORDER BY news_date DESC LIMIT 1;
+----+-------------+----------+-------------+--------------- ---------+------------------------+---------+------+------+- ------------------------------------------------------------ ---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------------+--------------- ---------+------------------------+---------+------+------+- ------------------------------------------------------------ ---------+
| 1 | SIMPLE | news_tmp | index_merge | news_position,news_cat | news_position,news_cat | 1,1 | NULL | 3 | Using intersect(news_position,news_cat); Using where; Using filesort |
+----+-------------+----------+-------------+--------------- ---------+------------------------+---------+------+------+- ------------------------------------------------------------ ---------+
1 row in set (0.00 sec)

After adding new index (cat_position):
explain SELECT news_id,news_title,news_summary,news_image_small FROM news_tmp WHERE news_cat='1' and news_position='7' ORDER BY news_date DESC LIMIT 1;
+----+-------------+----------+------+---------------------- ---------------+--------------+---------+-------------+----- -+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------------- ---------------+--------------+---------+-------------+----- -+-----------------------------+
| 1 | SIMPLE | news_tmp | ref | news_position,news_cat,cat_position | cat_position | 2 | const,const | 2 | Using where; Using filesort |
+----+-------------+----------+------+---------------------- ---------------+--------------+---------+-------------+----- -+-----------------------------+
1 row in set (0.00 sec)

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;

explain SELECT news_id,news_title,news_date FROM news WHERE news_cat='1' ORDER BY news_date DESC LIMIT 5;
+----+-------------+-------+------+---------------+--------- -+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------- -+---------+-------+-------+-----------------------------+
| 1 | SIMPLE | news | ref | news_cat | news_cat | 1 | const | 46664 | Using where; Using filesort |
+----+-------------+-------+------+---------------+--------- -+---------+-------+-------+-----------------------------+
1 row in set (0.00 sec)

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

explain SELECT news_id,news_title,news_image,news_summary FROM news WHERE news_position='2' ORDER BY news_date DESC LIMIT 12;
+----+-------------+-------+------+---------------+--------- ------+---------+-------+-------+--------------------------- --+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------- ------+---------+-------+-------+--------------------------- --+
| 1 | SIMPLE | news | ref | news_position | news_position | 1 | const | 34438 | Using where; Using filesort |
+----+-------------+-------+------+---------------+--------- ------+---------+-------+-------+--------------------------- --+
1 row in set (0.19 sec)


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;

explain 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;
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
| 1 | SIMPLE | news | ref | news_position,news_cat | news_cat | 1 | const | 46665 | Using where; Using filesort |
+----+-------------+-------+------+------------------------+ ----------+---------+-------+-------+----------------------- ------+
1 row in set (0.09 sec)

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.

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.

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;

explain SELECT news_id,news_title,news_position FROM news WHERE news_cat='5' ORDER BY news_date DESC LIMIT 6;
+----+-------------+-------+------+-----------------------+- -------------+---------+-------+-------+-------------------- ---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+- -------------+---------+-------+-------+-------------------- ---------+
| 1 | SIMPLE | news | ref | news_cat,cat_position | cat_position | 1 | const | 18169 | Using where; Using filesort |
+----+-------------+-------+------+-----------------------+- -------------+---------+-------+-------+-------------------- ---------+
1 row in set (0.00 sec)