mysql faster way of matching an inner string

Question

I'm trying to match on band names in a DB by excluding 'The'

So a search for 'The Beatles' or 'Beatles' would both succeed.

This is too slow: select * from artists where artist_name LIKE '%beatles';

Better ways to do this? I'd like to avoid having an extra sorting/matching column with 'the' stripped out.

Thanks!

Answer 1

Text searching should be handled using Full Text Search (FTS), either with native FTS or 3rd party (IE Sphinx).

Answer 2

• Try a fulltext index to index the artist column
• Use a external indexing tool like Sphinx. This will add another tool and index, but it is capable of really good and fast searching.

Answer 3

See my presentation Practical Full-Text Search in MySQL that I did for the MySQL University webinar series.

I compare several solutions, including:

• MySQL FULLTEXT indexing
• Apache Lucene (though I would recommend checking out Solr)
• Sphinx Search
• Inverted indexing
• Google Custom Search Engine (CSE) and similar search services