DelphiFAQ Home Search:
General :: Databases :: mysql
General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Implementing a full text search with mysql

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')
  
 

Generated 8:00:46 on Oct 26, 2020