DelphiFAQ Home Search:

Implementing a full text search with mysql

 

commentsThis article has not been rated yet. After reading, feel free to leave comments and rate it.

Question:

How can I implement a full text search with mysql?

Answer:

A database like mysql is not really suitable to do full text indexing/ searching but since version 3.23.23 it is possible a bit better than before.

Let's say you want to search for the terms 'mouse' and 'frame'. The important new keyword is MATCH

See the queries below:

/*
   traditional SQL:
   slow since it cannot use the index.
   mysql has to read the full table
*/

SELECT * FROM mytable
WHERE    textcolumn LIKE '%mouse%'
OR       textcolumn LIKE '%frame%'

/*
   since 3.23.23:
   This doesn't find partial words like MOUSEPOINTER.
   No ordering necessary; mysql automatically 
   sorts by relevance
*/
SELECT * FROM mytable
WHERE    MATCH textcolumn AGAINST ('mouse frame')
 

Comments:

2012-11-12, 03:54:24
anonymous from United States  

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: