CODEWITHSUNDEEP
sqlmysql
djburdick
djburdick

Reputation: 11940

mysql faster way of matching an inner string

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!

Upvotes: 2

Views: 1171

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

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

Upvotes: 5

Sjoerd
Sjoerd

Reputation: 75588

  • 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.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332561

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

Upvotes: 3

Related Questions