Efficient full-text searchs on large sets of data

[shadesogrey]

A database application I've written uses a table with around 600,000 rows. Each row has a text field 500-5000 characters long. I periodically need to find all the rows containing a particular phone number, name, or address, ie.'123-4567', 'john smith', '1950 Main St N

Comments

[el_diavalo]

I'm no MySQL optimization expert, but it almost sounds like you need a complete restructuring. Breaking out the phone, name, address, etc to their own fields, and searching for that specific one.

At that point, you can 'SELECT `date`, `name` FROM `tb_archive` WHERE `name` LIKE '%john smith%' ORDER BY `date` DESC"

Excluding the giant `text` field in the results would also speed up returns at that point.

It would take a lot of work, but it'd be worth it in the long run, IMHO.

[awfief]

It's slow because there's no index that can be used. Fulltext indexing can be used to get exact matches only. You can change the default of 4 characters being the minimum size of a "word" if you want

Stored procedure for Full Text

[shantanuo]

I have posted my answer on my blog....

http://oksoft.blogspot.com/2010/10/mysql-case-study-179.html