Implementing a full text search with mysql

Databases
InterBase (28)
MS-SQL (5)
mysql (33)
Oracle (1)

Exchange Links

About this site

Links to us

Implementing a full text search with mysql

This 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:


	Search: