Re: Full-text search slower than like

[Philipp Prelicz]

CREATE TABLE `pilz_pilz` (
`PilzID` int(11) NOT NULL,
`Pilz` text NOT NULL,
PRIMARY KEY (`PilzID`),
FULLTEXT KEY `Pilz` (`Pilz`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

---

Explain SELECT *
FROM pilz_pilz
WHERE MATCH pilz AGAINST ('xyz*' IN boolean MODE)

id...select_type...table...type...possible_keys...key...key_len...ref...rows...Extra
1....SIMPLE.....pilz_pilz.fulltext....Pilz........Pilz...0................1...Using where

---

SHOW TABLE STATUS LIKE 'pilz_pilz'

Name.......Engine....Version.....Row_format...Rows...Avg_row_length......Data_length......Max_data_length
pilz_pilz..MyISAM......10..........Dynamic...134018....36......4947240..281474976710655....6019072

Index_length...Data_free...Auto_increment....Create_time...........Update_time.........Check_time.........Collation.......Checksum
0................NULL..................2009-12-05.20:13:23...2009-12-05.20:13:25..2009-12-05.20:13:39....latin1_swedish_ci..NULL

---

Following statement with "match against" needs 1 to 1.5 seconds

SELECT *
FROM pilz_pilz
WHERE MATCH pilz AGAINST ('bole*' IN boolean MODE)
LIMIT 0 , 30

whereas the like version only needs around 0.003 seconds

SELECT *
FROM pilz_pilz
WHERE pilz LIKE '%bole%'
LIMIT 0 , 30

---

The table contains 134,018 rows.
The column pilz has around 2 - 4 words (many of the words are unique or only occure in a few rows)

I already tested this on two different servers - same results. (1. my home server with windows; 2. shared hosting on linux)

---

best regards
Philipp