Hi,

I have a table of a few columns of text that need to be searched (and the usual dates and primary of course). Something like below:

Column1 - Varchar(255)
Column2 - Varchar(255)
Column3 - Varchar(255)
Column4 - Varchar(255)

There'll only be 2000 rows.

1. So, do I use normal INDEX or FULLTEXT for the columns?

2. If I use LIKE to query, can I index the columns with FULLTEXT, or must only use normal INDEX?

Thanks a lot.

use the LIKE clause

FULL TEXT is for searching true text fields

_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google

Ok, I'll use LIKE.

So for the index of the fields... I use the normal INDEX instead of FULLTEXT right? What if LIKE clause is used to query fields with FULLTEXT index, the index won't be effective?

Thanks.

check the manual for the index sizes, but i believe that the index is limited to 255 characters which in a large field don't make sense

_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google

So from what I understand here, FULLTEXT index on the fields is not suitable right.

I use LIKE to query the four fields and then return the whole rows with matches. Similar to this:

WHERE field1 LIKE '%example%' OR field2 LIKE '%example%' OR field3 LIKE '%example%' OR field4 LIKE '%example%

all fields are varchar(255) and only 2000 rows

So the best way to index the fields is using normal single column INDEX for each of the fields? or one multiple column INDEX (four fields)?

You can create multiple indexes, mysql will pick just one to use, but the optimizer usually picks the most appropriate index to use for that query

_________________
Lostboy

Cat, the other other white meat

Please read Posting Etiquette before posting

You can always try Google