Should I use MATCH AGAINST or LIKE function?

hsy · Joined: 15 Nov 2006 Posts: 7

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.

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

use the LIKE clause

FULL TEXT is for searching true text fields

hsy · Joined: 15 Nov 2006 Posts: 7

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.

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

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

hsy · Joined: 15 Nov 2006 Posts: 7

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)?

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

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