Tweaking LIKE '%..%' on TEXT with InnoDB tables

[Uli B.]

Hi all,

I am writing an application that stores news articles in a MySQL database. Text is stored in VARCHAR/TEXT/MEDIUMTEXT fields. I am using InnoDB-Tables to ensure referential integrity on MySQL 4.x. The articles have an average length of 3.500 characters. There will most likely never be more than 1000 records.

The application performs a simple search like this:

SELECT * FROM tbl WHERE field LIKE '%keyword%'

A keyword would usually be 3 to 15 characters in length, I guess.

How can the search speed be optimized for this particular application ?

Does an index on a TEXT field make any sense at all when starting with a wildcard ? (LIKE '%...')

If an index does make sense then which would be the best key length in this scenario ?


To be more accurate: The SQL query looks like this:

$k = addslashes($keyword); //PHP

SELECT * FROM tbl WHERE headline LIKE '%$k%' OR short_text LIKE '%$k%' OR long_text LIKE '%$k%' OR city LIKE '%$k%' ORDER BY datetime_insert DESC

(LIMIT x,y might be appended but not always)


data types:

headline, city: VARCHAR(255), both indexed
short_text: TEXT
long_text: MEDUIMTEXT
datetime_insert: DATETIME


There is no join on other tables as you can see.


Any help appreciated,
Thx in advance,

Uli