MySQL Forums
Forum List  »  Performance

Tweaking LIKE '%..%' on TEXT with InnoDB tables
Posted by: Uli B.
Date: February 14, 2005 02:53PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Tweaking LIKE '%..%' on TEXT with InnoDB tables
2469
February 14, 2005 02:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.