What would be the most efficient way to do this search (mysql or text)?

Question

Suppose I have 500 rows of data, each with a paragraph of text (like this paragraph). That's it.I want to do a search that matches part of words. (%LIKE%, not FULL_TEXT)

What would be faster?

1. SELECT * FROM ...WHERE LIKE "%query%"; This would put load on the database server.
2. Select all. Then, go through each one and do .find >= 0 This would put load on the web server.

This is a website, and people will be searching frequently.

Answer 1

This is very hard for us to determine without knowing:

• the amount of text to search
• the load and configuration on the database server
• the load and configuration on on the webserver
• etc etc ...

With that said i would conceptually definitely go for the first scenario. It should be lightening-fast when searching only 500 rows.

Answer 2

You can use a full text search if you use myisam engine. http://dev.mysql.com/doc/refman/5.1/en/fulltext-query-expansion.html