mySQL Searching ¦ Best Practice

LIKE ¦ MATCH('') AGAINST ('') ¦ Others

[ukgimp]

Any advice on choosing the best method to search two fields within a mySQL db.

The LIKE command seems to be quick but I dont have that many records at the moment. There is also no obvious way of ranking the results

The MATCH AGAINST option seems to work OK providing I only want to search one field despite the fact that one is VARCHAR and the other is TEXT so it should work.

I suppose I am looking for the best method to search a DB and return results for both single and phrase searching. I an after long term scalablity, I dont want 45 second search returns in the future.

Suggestions / Advice.

Regards

[dingman]

Long-term speed might be improved by creating an index on the field(s) you're searching through in the database. I'm not familliar with match() against(), so I can't help there.

[ukgimp]

Does mySQL/php allow searching of more than one field using "match"

the evidence seems to suggest yes, but this does not seem to be the case. Both fields have been altered to Fultext but in the "field" column within phpMyAdmin for one of the fields has an extra number in it "1".

Any ideas

Cheers

[Dreamquick]

If you are using a SQL statement then you might want to consider using the ORDER BY clause as that will sort out your ranking woes...

-Tony