Full Text Search vs. Standard SQL search

[JonasE]

Hello all,

 

I am in the middle of developing a document management system (think "low level library system"). I estimate there to be no more than 50000 records/rows in the main tables of the database. At no time will more than 5 users be running searches at the same time, and there will only be one person adding or updating records.

I have a few questions regarding how I should implement search functions in this system.

 

1. For efficiency and speed. Given the estimates above, should I use mySQL's full text search functions, or simply run standard sql queries to retrieve my data? Will performance suffer greatly if I use standard sql queries?

 

2. From my understanding there are problems retrieving certain characters such as "++" and "#" using full text search. Is that correct?

 

3. I will need to retrieve data in some searches from different tables. In effect, I will need to select data from "table1" and "table2" with a join and use that as the return of a search. Can this be done with full text searches?

 

Thanks for any advice you can offer,

 

Jonas

[fenway]

1. For "text" searching, full-text works great... as opposed to LIKE '%whatever%' which is horrible.

2. Correct, they are simply ignored.

3. You can't full-text search across tables... you'd have to make a pseudo-table with the relevant text values from all of the "pieces" and index that table.