Multiple Columns with different Values OR Single Column with Multiple Values?

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Everything works well if database is small. But now i have 20 millionrecords in my database. Taking an exmaple there are 5million recordwith col2='engine' and only 1 record with col3='toyota', it takesubstantial time to find 1 record.

I was thinking this i canaddress this issue if i merge both columns in a Single column, but icannot figure out what format i save it in single column that i can usequery to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?

What about creating a composite index on col2 and col3? Maybe that will sort out the problem without you having to try and combine the 2 columns.

[quote user="martins"]
What about creating a composite index on col2 and col3? Maybe that will sort out the problem without you having to try and combine the 2 columns.
[/quote]
I can try this but i still need to create 2 different CONTAINSTABLE, right?
if i am not wrong result of both CONTAINSTABLE is independent mean both CONTAINSTABLE will return all records before performing any criteria?
Because i don't want system to search through 5 million record which may match with first criteria but only return 1 record due to my second criteria.

I'm not too familiar with the CONTAINSTABLE function, but had a quick look in BOL. As far as I can see you can do it with one CONTAINSTABLE. Since you are not weighting your results, how about doing it without the CONTAINSTABLE function and rather a col2 like '%engine%' and col3 like '%toyota%' in your select statement. Then an index will definitely help.
Also have a look at your execution plan. Doing it the way you are currently might mean that SQL Server is scanning the table twice.

Sorry, my bad...you won't be able to do it with one CONTAINSTABLE if the data is in two different columns.
Also, have a look at using CONTAINS rather than CONTAINSTABLE if at all possible.