469,272 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

Google style search

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

Jan 6 '06 #1
1 1968
> 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
Jan 6 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by vizziee | last post: by
reply views Thread by tim | last post: by
1 post views Thread by xahlee | last post: by
8 posts views Thread by nil | last post: by
2 posts views Thread by john | last post: by
9 posts views Thread by artistography | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.