#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171

    Best MySQL search pracrise on TEXT and LONG TEXT columns


    Hello;

    I need to get some comments and advice on best search practises for querying text or full text columns in multi million record tables (InnoDB storage engine, so I guess full text index is not the way to go. Server version: 5.1.44 and +).

    I have 3 questions I appreciate if anyone helps

    1 - What sort of index do you recommend?
    2 - What is the best practise in regards to the query? For example I use something like this to search for phrases or words but I have heard there are better ways:
    Code:
    SELECT description
    FROM   `webmoosh_posts`
    WHERE  `description` LIKE '%derived tables are%'
    3- In general what else should I consider? Any unexpected behaviour as it is a different data type?

    Thank you
    Last edited by zxcvbnm; May 2nd, 2013 at 05:11 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    1 - none... too expensive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by r937
    1 - none... too expensive
    Hello;
    Thank you for the unexpected response. How would you improve the performance for the search feature then?
    Thank you
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by zxcvbnm
    How would you improve the performance for the search feature then?
    who, me?

    i would reconsider the advisability of allowing my application to permit searching a TEXT column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by r937
    who, me?

    i would reconsider the advisability of allowing my application to permit searching a TEXT column
    Huh! How do you search for a phrase in the description of a product?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Huh! How do you search for a phrase in the description of a product?
    probably with a LIKE against a VARCHAR(255) column

    remember, if you want to do something and it hurts, you can either do it and put up with the pain, or don't do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    Originally Posted by r937
    probably with a LIKE against a VARCHAR(255) column.
    That's doable and I am testing it, thanks.

    Do you consider Full text index is very popular (with MATCH AGAINST) for MyISAM engines?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Do you consider Full text index is very popular (with MATCH AGAINST) for MyISAM engines?
    yes, it is very popular

    considering that it doesn't work on other engines

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    MySQL is not optimized for searching large amounts of natural language text, and is therefore not capable of doing it quickly against a large data set. The closest you can get in MySQL is a fulltext index on a MyISAM table; there is no equivalent in InnoDB as far as I know.

    If you have millions of relatively large natural language documents that you want to make quickly searchable, you need to use a specialized searching solution, like SOLR or Xapian, which are specifically designed and optimized for the purpose of searching large bodies of text. The relevancy of the results returned by these systems is generally a lot better than those returned by MATCH_AGAINST too.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo