slow full-text-search in MySQL

Question

I have table with +800k records with simple schema (just id [int] and full text index on note [text]). Every time I insert new record I use php script to strip all strings shorter than 4 chars and all stop words. Than the processed string is inserted in DB. When I search for some common keyword it takes +4s to get result and whole DB is stuck during the search. What am I doing wrong?

//edited

schema:

id int(11) NOT NULL,
text mediumtext COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (id),
FULLTEXT KEY text (text)

query:

SELECT table_name.id FROM table_name WHERE MATCH (text) AGAINST ("+keyword" IN BOOLEAN MODE)

Can we see the schema and what query is slow? (Aside: presumably you mean words shorter than 4 chars?) — bobince
I've added schema and query (Aside: yes, sorry for that :) ) — Mr Black
The 4 secs time is just the query time or php time to retrieve the values? — guido

Ariel Ariel · Accepted Answer · 2011-07-31T10:40:45

Are you doing

WHERE note LIKE '%keyword%'

?

Because if you are, I'm not surprised it's slow.

You have two choices. Either break it into individual works, and store each word by itself in the database, along with an ID, and then link that ID to the note_id.

Or, read this: http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html and add an index of type FULLTEXT to your database.

slow full-text-search in MySQL

2 Answers