Faster search String in Mysql Database

I have a large DB having > 20,000 rows.开发者_如何学C I have two tables songs and albums:

table songs contains songid, albumid, songname and table albums contains albumid, albumname

Currently when user searches for a song I give results instantly as soon as he starts typing. Just like Google Instant.

What I am using is: Everytime user types I send that query string to my backend php file and there I execute that query in my DB like this:

SELECT * FROM songs, albums WHERE songs.albumid = albums.albumid AND songs.songname LIKE '%{$query_string}%';

But it's very inefficient to use DB queries everytime and also it's not scalable as my DB is growing everyday.

Therefore I want the same feature but faster and efficient and scalable.

Also, I dont want it to be exact pattern matching, for example:

Suppose, if user types "Rihana" instead of "Rihanna" then it should be able to give the results related to Rihanna.

Thanks.

You should index table Songs songname column on first n chars, say 6, to get better performance for the query.

Trigger the query search only after n chars have been typed, say 3 (jquery autocomplete has this option, for example)

You may also consider an in-memory DB if performance is truly crucial (sounds like it is) and the amount of data will not consume too much resident memory.

Google, btw, does not use a legacy RDBMS to perform its absurdly fast searches (continually amazed...)

First of all you should find MySQL's FULLTEXT search support far faster than your current approach.

I suspect with the kind of speed you'd like from this solution and the support for searching for mis-spelled words that you'd be better off investigating some kind of more featured full text search engine. These include:

• Sphinx Search
• Solr
• ElasticSearch

Try full text search.

Indexing requires MyISAM tables though.

If you need ACID and full text search, use PostgreSQL.