Google style search

[heath]

i'm building a website for a band and each section of the site, i want
to have google style searches. mainly, for the news and tour section.
i'm using mysql 4.0.26 and php 4.0 for all web content. below is the
query i created for the tour search (this example looks for 3 search
items). php builds this dynamically but for each search term, there is
an extra statement in the where clause. additionally, there are 5
table joins but i left them out for sake of brevity.

after building this query, i've been reading up on full text searches
in mysql. BUT, i have seen some people complain that it is slow
(however, i do note that they are speaking of tables with millions of
records). also, i would like to include the date within the search but
i assume i'd need to create another field in each table that is a
textual representation of the date field already in the table and then
create a fulltext index on that field

so, my question is, should i stick with this query or build the indexes
and rewrite full text queries? or, is there another method i should
employ -- is my query slow/dumb/ineffecient? this is for a local band
so i do not expect the database of shows to ever reach more than
1000-2000 (and probably about that for the linked tables of other bands
and venues). also, i don't imagine the traffic on this site to ever be
more than a handfull of users at a time. however, i also hate
inefficient code and if the band gains a lot of popularity, i'd love
for this code to still work and be portable enough to use for any size
band.

SELECT DISTINCT `Shows`.`Show_I D`
FROM `Shows`
WHERE CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item1% '
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item2% '
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item3% '

thanks,

heath

[Markus Popp]

> LIKE '%search_item1% '

You should avoid writing queries like this (if possible) because MySQL can
never use an index for such queries. I think, the fulltext index is still
the best choice that you have in your particular situation.

Markus