SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Speeding Up Site Search Query

    I have a site search query right now that is noticeably slower than most queries on our site despite similar sizes. My search queries use LIKE comparisons where other queries are direct match or at least utilize some indices. I would love to utilize a full text search for the columns I am searching, but the columns are on a variety of tables (6 or so). Is there anything I can do to speed up these queries, or am I stuck with sluggish performance?

    Code MySQL:
    SELECT <cols, no evil stars here>
      FROM products
    <multiple joins snipped>
     WHERE products.active = 1
       AND (
                products.code LIKE '%acme%'
             OR products.name LIKE '%acme%'
             OR designer.name LIKE '%acme%'
             OR collection.name LIKE '%acme%'
             OR category.name LIKE '%acme%'
             OR parentcategory.name LIKE '%acme%'
             OR product_size.prompt LIKE '%acme%'
             OR fabric_type.fabric_name LIKE '%acme%'
           )
       AND (
                products.code LIKE '%blue%'
             OR products.name LIKE '%blue%'
             OR designer.name LIKE '%blue%'
             OR collection.name LIKE '%blue%'
             OR category.name LIKE '%blue%'
             OR parentcategory.name LIKE '%blue%'
             OR product_size.prompt LIKE '%blue%'
             OR fabric_type.fabric_name LIKE '%blue%'
           )
       AND (
                products.code LIKE '%widget%'
             OR products.name LIKE '%widget%'
             OR designer.name LIKE '%widget%'
             OR collection.name LIKE '%widget%'
             OR category.name LIKE '%widget%'
             OR parentcategory.name LIKE '%widget%'
             OR product_size.prompt LIKE '%widget%'
             OR fabric_type.fabric_name LIKE '%widget%'
           )

    Just for reference, a standard (ie. faster) query might look something like:

    Code MySQL:
    SELECT <cols, no evil stars here>
       FROM products
     <multiple joins snipped>
     WHERE products.active = 1
       AND designer.id = 2
       AND (
                category.cat_id = 1
             OR parentcategory.cat_id = 1
           )

    Open to any suggestion, but there are (of course) limitations to how much I can rewrite.
    MySQL v5.1.58
    PHP v5.3.6

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i think you're stuck with a table scan of all joined tables
    r937.com | rudy.ca | 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
  •