ILIKE '%term%' and Performance

[csn]

I'm thinking of enabling searches that use queries like "select * from items where title ilike '%term%'". The items table has tens of thousands of rows. Is it worth worrying about the performance of such a query (since, if I'm not mistaken, it will never use indices). If it is, what's the best option - use tsearch? How does tsearch (or whatever else) compare performance-wise to not using it, or to typical index-based queries for that matter?

[kiropyckoticks]

Maybe, you can compare performance issues in queries by viewing the MySQLAdmin Health Portion. Whenever you execute a mysql query, you can see how much resource your computer is using for that query.

[Roger_Ramjet]

Dunno what you mean by 'tsearch' ?? Never heard of it myself.

Yes, using a wildcard search with LIKE will involve a table scan and that could be slow - if you had a million records or so that is. Searching through a few tens of thousands of records should not be a problem. If it is then that will be because your shared server is overcrowded or just plain slow. Change host to solve that problem.

If you did have a large database then using full text search would improve performance.