tips for php/mysql search methods?

[c0rrup]

Currently I'm using a basic full-text searching with my php / mysql download script, its a basic system but I'm seeing that full-text searching isn't that great and wondering if there are a few tips on how I can improve the quality of it?

Thanks.

c0rrup Reviewed by c0rrup on 26th Mar 2011. tips for php/mysql search methods? Currently I'm using a basic full-text searching with my php / mysql download script, its a basic system but I'm seeing that full-text searching isn't that great and wondering if there are a few tips on how I can improve the quality of it? Thanks. Rating: 5

[bhanuprasad1981]

vbulletin you can try installing spinx

[cyber-cliff]

or you can try google.. google is your friend..

[Jueki]

You can try looking into the Zend Lucene Search as it's easy to implement in almost any PHP project.
It's creating a search index and you can easily customize the search results / indexed data to your need.

[GrandBucks]

What is your problem exactly? I think that fullsearch engine in mysql is good enough. If there is the perfomance problem you can try standalone indexing software like Sphinx...

[Lock Down]

The only way you are going to get quality returns is to use like instead of match against boolean in the select statement.

[c0rrup]

LIKE is actually very inefficient on large datasets as it has to scan the entire index whereas FULLTEXT doesn't (since it is made for searching, LIKE is not). You can see this by EXPLAINing a query, it will check every row in the table

I'm trying out a few query changes now that might help, but anymore tips would be nice.

EDIT:
problem sorted now thanks to JmZ

[Lock Down]

Hmm. I have 280,000 posts and it eats through it in seconds.

You have to make sure you have full text indexing also.

Glad you got it working to your liking tho.

[c0rrup]

You must mean milliseconds, if a query takes seconds, it is not a good query.

Also, LIKE shouldn't make use of a FULLTEXT index and most probably doesn't.
Try a few million rows, then the difference in performance is clear, LIKE is terrible for searching.

[Lock Down]

Sorry don't have a few million on site.

You obviously are talking on a dedicated server. Not a shared server

I tried your site 5 times during the day and it took over 4 seconds to load each time. Seems long for the small amount of queries and small amount of images.

Also searched for movie and it took 8 seconds to load. Hmmm..