MATCH AGAINST, why does it work without full text indexes?

[niclas]

I'm using a query like this:

SELECT firstname, lastname, mail, phone
FROM clients 
WHERE company_id='$company_id' AND
(MATCH(firstname,lastname,mail,phone,custom1,custom2,custom3) AGAINST ('$queryfixed' IN BOOLEAN MODE))
ORDER BY firstname, lastname, mail

 

$queryfixed is the query changed from "sam jones" to "+sam* +jones*"

 

Currently I have no full text index at all. All I have is normal indexes on mail, company_id and phone.

This is a table of around half a million rows, but each company_id has around 500-2000 clients, and most queries executes fast.

 

Is it correct that it works without any full text indexes at all?

 

I tried making a full text index with firstname, lastname, and there was no big time difference when I run it on a single company.

Users mostly search for first and last names, but sometimes also the custom fields.

 

Should I make an index convering all columns that are in the MATCH(...) clause, or only on the most frequently used, like first/last name?

I'm happy for any tips on how to optimize this, as the database grows!

 

I'm using version: 5.0.90

[mikosiko]

Ref: http://dev.mysql.com/doc/refman/5.5/en/fulltext-restrictions.html

 

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table,

unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.

[niclas]

Interesting, thanks. So would it help if there is a fulltext index on (firstname,lastname) or does it still have to scan all rows? Seems logical that it would need to scan everything, since finding a match in any of those two wouldn't show all hits.

[fenway]

It's silly to use it without an index.