CODEWITHSUNDEEP
mysqlfull-text-searchsearch-engine
ari
ari

Reputation: 43

mysql not a good search engine

I'm trying to create a search engine for our library. Currently I have table call book_tittle where the column contain:

id (int)
book_tittle (varchar 256)
book_description (varchar 256)
author_id

When someone search book title, for example he type: "big bird", I simply search from table where book_title like '%big bird%'.

If I have a book titled "big bird fly" this simple query was enough, but unfortunately when I have a book titled "big blue bird", the query will return nothing.

Can someone suggest me how to solve it? So that if someone search for "big bird", he will get "big bird fly" book and "big blue bird" book.

Upvotes: 0

Views: 167

Answers (5)

GolezTrol
GolezTrol

Reputation: 116100

If you modify your field to a TEXT field instead of VARCHAR, you can use a FULLTEXT index. Then, you can use MATCH instead of LIKE to match a string. This match is much more flexible. You can match for big bird to match the words big and bird or you can match for "big bird" if you want to match the words together. You can also use wildcards, like bir* to match bird and birth.

Upvotes: 1

Vilx-
Vilx-

Reputation: 106902

Have you tried MySQL's built-in fulltext support?

Upvotes: 0

duffymo
duffymo

Reputation: 308733

You're right - relational databases don't make good search engines.

You need something like Lucene. You'll have to ask it to index your data so you can do searching.

Upvotes: 0

Fokko Driesprong
Fokko Driesprong

Reputation: 2250

You can rewrite your query like:

WHERE book_title LIKE '%big%' AND book_title LIKE '%bird%'

This can easly be done in your code. I don't know how large your book table is, a full-text index will probally be faster.

MySQL can also implement full-text, you will need to use the MyISAM engine. Lucene is more efficiënt tough.

Upvotes: 1

Piotr
Piotr

Reputation: 4963

You should use a search engine. Check out Lucene. It is available for PHP, .NET, Java etc.

Upvotes: 1

Related Questions