shadesogrey
in
mysql
Efficient full-text searchs on large sets of data
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'
I'm doing this using
SELECT * FROM `tb_archive` WHERE `text` LIKE '%john smith%' ORDER BY `date` DESC
The problem is that it is too slow. Most searches take 30-60 seconds. If multiple searches are done the server response begins to slow to a crawl for other users.
I've looked at mysql built in full-text indexing - but I'm not sure if it can work since I only need exact matches, don't care about relavence, and often search for numbers and short words.
Any suggestions as to how I can do this more efficiently?
I'm doing this using
SELECT * FROM `tb_archive` WHERE `text` LIKE '%john smith%' ORDER BY `date` DESC
The problem is that it is too slow. Most searches take 30-60 seconds. If multiple searches are done the server response begins to slow to a crawl for other users.
I've looked at mysql built in full-text indexing - but I'm not sure if it can work since I only need exact matches, don't care about relavence, and often search for numbers and short words.
Any suggestions as to how I can do this more efficiently?