Is This Fulltext Query Ugly or What

Databases
1

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

2

get rid of the $limitednews array, and combine your queries…

SELECT news.title
     , news.link
     , news.description
     , news.date
     , related_sites.site_name
  FROM news
INNER
  JOIN related_sites
    ON related_sites.site_id = news.site_id
 WHERE news.news_id IN
      ( SELECT news_id
          FROM news_column
         WHERE news_column.date >= CURRENT_DATE - INTERVAL 30 DAY )
   AND MATCH(news.title, news.description)
       AGAINST('$keyword' IN BOOLEAN MODE) > 3
ORDER
    BY news.news_id DESC LIMIT $min,10
3

Wow! I just ran an explain query to test that and it responded so much faster. Damn you’re good. Going to impliment now and see what my server management says.

Cheers!
Ryan

4

Figured I’d post in an update on this.

When I ran an “explain” on the original query my database literally took about 10 seconds to respond. My logs claimed the lock_time took 0.003512 seconds. Then I ran the updated query written by you and my database responded almost instantly. I looked at my mysql log of extensive queries and saw that one had a lock_time of 0.000061.

So definite improvements. Better yet, our database gave us no issues today, when typically we see a few daily. I think the stress of this query is finally off its back.

Cheers!
Ryan

5

good news :slight_smile: