I have a newsdesk type section on my site that finds indexed news articles relating to keywords. My server guy said, even with the few queries run (in comparison to the rest of the site), the SELECT query is still creating a slowdown.
To help improve performance, I first select all the Primary column_ids for articles indexed in the last month.
$ln= mysql_query("SELECT news_id FROM news_column WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date",$conn);
I put all those IDs collected into an array, setup a list of ids with the array under variable $limitednews
Then I run this query with variables processed
$search = mysql_query("
SELECT news.title, news.link, news.description, news.date, related_sites.site_name
FROM news,related_sites
WHERE news.news_id IN ($limitednews) AND MATCH(news.title, news.description) AGAINST('$keyword' IN BOOLEAN MODE) >3 AND news.site_id=related_sites.site_id ORDER BY news.news_id DESC LIMIT $min,10
",$dbh
Now, is this ugly or what? The time this takes and resources used seem insanely high. And, just in case you are wondering, the $limitednews variable probably has some five-thousand ID numbers listed in it for the query to go through. Efficient, or horrid?
All feedback appreciated.
Cheers!
Ryan