Forum Moderators: open
The columns are text + varchar + smallint + decimal
Now fulltext search can only be done on text + varchar + char types, so I would have to use a mix of LIKE, and MATCH and it doesnt produce the results needed (been there done that).
So i was thinking, what if i took all the columns and put their data into a new table that only contained a ID (foreign key to the original table primary key) and a value field (text).
This value field would contain all the fields from the original table combined (seperated with a space).
Then I could index this field and perform a search on it.
Example:
Say I have car table with the following columns:
car_id - int (pk)
model - varchar(50)
year - smallint(4)
hp - decimal(10,5)
torque - decimal(10,5)
I could create a search table with the following columns:
car_id - int (fk)
value - text
Then add all the above columns to the value column, index the value column as fulltext and then perform search on it.
Thoughts? Good/bad? etc?
Maybe i should provide a summary of my long post:
1) Want to search across multiple columns and types. Thinking on joining all columns into a single column on a seperate table. Pro/cons?
2) Alternative: Create a view that joins these multiple columns into one and perform search on the view. Worried about the on-fly conversion/merging performance. pro/cons?
Id like a single input field that can perform a search and provide results in a much better fashion than google can.
Just like youtube provides preview pictures next to the videos, I can provide much more additional data than google can provide.
