SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    CA
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow Search Query - How to Speed Up?

    Background
    -----------
    I break up each article word for word and insert each non-common word in a search table. The search table contains a row for each word and the associated article id. article_id is indexed. The table contains about 4 million rows.

    Query
    -----
    Doing the following query takes about 4 seconds to run.

    SELECT COUNT(article_id) AS search_score, article_id
    FROM search_table
    GROUP BY article_id
    ORDER BY search_score

    Problem
    -------
    This query takes approximately 4 seconds to run. The EXPLAIN show that it's using 'temporary'.

    Question
    --------
    Is there anyway to be able to eliminate the 'temporary' from the EXPLAIN, or do I need to rethink how I do searches in general?

    Thanks.

  2. #2
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Considering you are grouping four million rows and then sorting off of the count, I'm not suprised that it is taking four seconds. About the only two ways I could see this query being speeded up is by reducing the number of records, or adding more horsepower under the hood.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    CA
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do other sites go about doing search queries? There must be something I'm missing. Maybe there is a way to add a score column so it's not necessary to do the count for every query?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you add a score column, then you will have to update it every time you add or delete an article

    how do other sites do it? i'll bet that if they even have a page where you can count 4 million rows grouped by article_id, that it takes them 4 seconds too



    you can speed up your query by removing the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you add a score column, then you will have to update it every time you add or delete an article
    Even so, I would think that would be better than calculating the score during each query.

    How often are new articles added? You might be able to get away with a daily or weekly update job that calculates and updates the new scores. That means you have one painful batch job running infrequenly, but your searches run much faster. In the end, it's probably a better solution for you.

    With the right index on the score, you wouldn't need an ORDER BY in the query either. That might get you a lot of perfomance boost.

    Just a thought...
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!


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
  •