SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is This Fulltext Query Ugly or What

    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.

    PHP Code:
    $lnmysql_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

    PHP Code:
    $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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    get rid of the $limitednews array, and combine your queries...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,760
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    good news
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •