improving mysql fulltext search - design thoughts

[darkage]

Im trying to perform a search on a number of columns in one table.

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?

[darkage]

Another design thought I've been having is to create a view on that particular table and search on the view, although im a bit worried regarding the performance of numeric->string conversions runtime.

[darkage]

Slow down with the replies :)

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?

[coopster]

How about a search page that allows multiple search options, one for each column? That way you can focus on just that column in your WHERE clause.

[darkage]

Thanks for the input, but im basically trying to implement google-like search on my website. Becaues of that I cannot/dont want a "difficult to use" search.

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.