Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    47

    Unanswered: Index where like

    Everytime I try to query using an index, it stops using the indexes if I use two percentage signs. Why is that and what can I do?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you mean

    ... WHERE foo LIKE '%bar%'

    you can't do anything, it is in the nature of this construct that all values of foo must be examined, hence there's no point to using an index

    the index will be used for this --

    ... WHERE foo LIKE 'bar%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    47
    Well I'm trying to write a search engine for my site, what options do I have? Can I opimize the query or anything?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you need to search all rows of the table to find those which match '%bar%' then that's already as optimized as you're gonna get
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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