SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIKE, RLIKE or FULLTEXT?

    Hi

    I am about to build a little search engine and I was wondering if experienced people could give me some advice. I was wondering which angle I should have:

    like?
    rlike?
    fulltext?

    RLIKE sounds good because of the use of RegExp. But LIKE or RLIKE have both the downside of being a pain when it comes to perform a search against multiple columns. My main problem with FULLTEXT is that it doesn't allow searches for words less than three chars. Hum... what about poor accrs and abbrs? Is there a trick I don't know? Like using wildcards maybe?

    thanks for your time and patience

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    give an example of the types of columns that would all be the target of a search

    would you look for "toledo" in address1 or address2 or aptno or city or state or country?

    or would it make more sense to search for "toledo" in city only?

    what about searches on title and synopsis and teaser and fullarticle? that makes more sense

    however, i will venture that there aren't too many searches where you have so many similar columns that need to be the target of a combined search

    so i would start with LIKE with ORs

    and consider moving to something else only when i discovered a situation that LIKE doesn't cover well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I won't search that many columns so I guess that I will start with LIKE. However, tell me if I am wrong, it would be a good idea to build an index at the beginning of the project (I am thinking long term here, I might need one day) for all the cols who might be searched.


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not an index for all columns, but an index on each

    even though the indexes might not be used if you are looking for something inside the column, LIKE '%something%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes exactly rudy

    so now I will try to figure out how to do it (which shouldn't be a problem) and I will post the table structure just to make sure that I haven't made any mistakes ruining my work in the future.



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
  •