MySQL search tactics

I'm implementing a search on my project, requirements:

• keywords could be found at two columns (tags & titles) from different tables
• #1 search tags for exact matching
• if empty, 2# search tags using MySQL SOUND LIKE feature
• if empty, 3# for each word search title开发者_运维技巧s using LIKE='%word%'
  • intersect titles results

Don't know if you get the idea. Hope so.

Now the problem. I read in a book that querys using LIKE and % at the begging wont use indexes for speed up.

If this is true, my formula will get slow while tags and titles increase.

What would you do in terms of performance?

Thanks,

Assuming MyISAM tables, you might want to look into implementing full-text search as an alternative to the LIKE.

I think in any "linear" type of search formula adding things like keywords/tags to search for will eventually cause it to become alot slower as far as searching goes. Isn't that the main problem with Relational Databases anyways? (correct me if im wrong)

If you want to stick with just your 2 tables and you want to do fuzzy text matching, have a look at the MATCH ... AGAINST. It will do more or less what you want. But there are some caveats.

• FULLTEXT indices can only be implemented on MyISAM tables, not INNODB
• if your text does follow standard word distribution, your results may not be optimal

If you're willing to go beyond a relational approach, an indexer like Solr might work for you.