Creating Search Functionality

[Drongo_III]

Hello

 

I soon need to build a php/mysql search feature for a website and I am hoping someone can nudge my research in the right direction.

 

The search functionality is essentially an index of pages. The database table will likely never hold more than 1000 rows max.

 

I read on the MYSQL website that using natural language full text searches are the way to go. However, it also states that where a table contains limited rows, and therefore where occurrences of a search term appear more than 50% of the time, this can return no results.

 

So here are my questions:

 

1. Is it correct to use natural language full text searches for a table with no more rows than around 1000?
    
2. If not, can anyone propose another method. For instance should I simply stick to using  LIKE searches?
    
3. Any tips for ensuring efficiency?

 

Any help you can provide is much appreciated as I've not tried to build a search function before so this is all a bit new!

 

Drongo

Edited February 10, 2014 by Drongo_III

[Drongo_III]

Ok so my first post didn't garner much of  a response but hopefully now I have more of an idea of what I need to achieve one of you bright sparks can nudge me on course.

 

I'm using php/mysql to create a search engine for a site i am working on.

 

I've resorted to using MATCH/AGAINST in Boolean mode (code snippet below) but I really am quite stuck on how I might go about ordering the results based on relevancy.  IF someone could point me in the right direction on this it would be very helpful :/

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE);

[jazzman1]

There is no real benefit of using "IN BOOLEAN MODE" against "FULLTEXT" search like in the example above. Not very familiar with this, but using boolean searches, you can easily determine what strings (or group of strings) should or should not appear in query results. If I modify a little your example the following query will return all rows where title and body columns contain "jazzman" but not "Drongo_3" 

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('+jazzman -Drongo_3' IN BOOLEAN MODE);

Plus sign indicates that the following string must appear in the matching rows, the minus sign - NO. There is much more to say but it depends on your wishes.