SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot darksystem's Avatar
    Join Date
    Jan 2005
    Location
    Fayettville, North Carolina
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help: "LIKE" query optimization

    Hello,

    Can anyone tell me if this is a good usage of "LIKE" query

    Code:
    select p.productid, p.prodname, p.prodcode, c.catname from products p, categories c
    where p.productid like "%acne%" or p.prodname like "%acne%" or p.prodcode like "%acne%" or c.catname like "%acne%" ORDER BY RAND();
    using "LIKE" condition in multiple fields to search on something is kinda
    messy to me. Are there any other better idea on how to optimize that query?

    Any help is highly appreciated.

    Regards
    Ebay API, OSC/CRE/OscMax/ZenCart/SEO Services
    Lucki Multimedia - Email

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the messiness can be mitigated, but only somewhat, like this...

    WHERE CONCAT(p.prodname,p.prodcode,c.catname) LIKE like '%acne%'

    but as for optimization, no, using LIKE with a leading wildcard is going to remain slow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot darksystem's Avatar
    Join Date
    Jan 2005
    Location
    Fayettville, North Carolina
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see. May I know what will be the best recommended query for the sql code that I provided above? The idea is to search(a keyword(s)) on products(product_id, product_name, product_code) and categories(categories_name) table

    Btw, I really appreciate your help.
    Ebay API, OSC/CRE/OscMax/ZenCart/SEO Services
    Lucki Multimedia - Email

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darksystem View Post
    May I know what will be the best recommended query for the sql code that I provided above?
    what you posted is about as good as it gets
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot darksystem's Avatar
    Join Date
    Jan 2005
    Location
    Fayettville, North Carolina
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was wondering with your comment on this one

    "leading wildcard is going to remain slow"

    is there anything i could to make it faster?
    Ebay API, OSC/CRE/OscMax/ZenCart/SEO Services
    Lucki Multimedia - Email

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not expert but maybe something like Lucene or some other search engine would be faster than MySQL in this case?

    That would require some benchmarks.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darksystem View Post
    "leading wildcard is going to remain slow"

    is there anything i could to make it faster?
    not without removing the leading wildcard, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot darksystem's Avatar
    Join Date
    Jan 2005
    Location
    Fayettville, North Carolina
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so it should be like something like this(below) to make it faster

    LIKE "acne%"

    the leading wildcard is now removed.

    will that make a great difference in terms of performance/optimization?
    Ebay API, OSC/CRE/OscMax/ZenCart/SEO Services
    Lucki Multimedia - Email

  9. #9
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LIKE "%foo%" will always be slow in MySQL

    LIKE "foo%" is faster, but your not getting all the results you need...

    If you really need the "%foo%" foo functionality, you need a good full text search engine.
    Try SOLR for that, it's free and works well with millions of records, on cheap web servers.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darksystem View Post
    will that make a great difference in terms of performance/optimization?
    yes

    but it will also make a huge difference in the results that you get
    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
  •