My Query is consuming an inordinate amount of processor time of the server's CPU

Question

The following query is used to show the "news" which has the highest Hits and dated during the last week.

Since the table now has more then 14000 records, the query is consuming an inordinate amount of processor time of the server's CPU

EXPLAIN: 1 SIMPLE news ref state state 4 const 20619 Using where; Using filesort

INDEXES:

Edit Edit Drop Drop PRIMARY BTREE Yes No id 20635 A No
Edit Edit Drop Drop state BTREE No No state 3 A No
Edit Edit Drop Drop priority BTREE No No priority 1 A No
Edit Edit Drop Drop date BTREE No No date 20635 A Yes hits 20635 A No Edit Edit Drop Drop cat FULLTEXT No No cat 1 Yes Edit Edit Drop Drop title FULLTEXT No No title 1 No
text 1 No

$timeago = strtotime("-1 week");

SELECT id, title, sub_title, date, image_url, hits, text, comment
    FROM news
    WHERE state = '2'
    ORDER BY date >= $timeago DESC, hits DESC
    LIMIT 6

My question is how can I manage to recode the query in a way that i can avoid mass consuming of CPU?

Answer 1

Use explain plan to see what is happening. You would at the very least create an index on date or on date and hits

Update Also below would be better I think,

SELECT id, title, sub_title, date, image_url, hits, text, comment
FROM news
WHERE state = '2' and date >= $timeago
ORDER BY date DESC, hits DESC
LIMIT 6

Answer 2

Well, the thing is, MySQL is having to go through all 14,000 records and sort them by time in order to complete your request. You need to index the date/time column to make this query more efficient, otherwise it's going to use a lot of resources and you'd have to upgrade the server significantly.