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_ID`
FROM `Shows`
WHERE CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Business_Name`, `Address`.`Street_Address`,
`Address`.`City`, `Address`.`State_Or_Province`, `Address`.`Country`,
`Band`.`Business_Name`, `Affiliate`.`Business_Name` ) LIKE
'%search_item1%'
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Business_Name`, `Address`.`Street_Address`,
`Address`.`City`, `Address`.`State_Or_Province`, `Address`.`Country`,
`Band`.`Business_Name`, `Affiliate`.`Business_Name` ) LIKE
'%search_item2%'
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Business_Name`, `Address`.`Street_Address`,
`Address`.`City`, `Address`.`State_Or_Province`, `Address`.`Country`,
`Band`.`Business_Name`, `Affiliate`.`Business_Name` ) LIKE
'%search_item3%'
thanks,
heath
