I'm doing an advanced search for an events database and my resulting SQL look like this:

SELECT events.event_id , events.name , events.description
FROM `events` , `event_city` , `event_dates`
WHERE 1 AND (
(
events.name REGEXP '[[:<:]]Art Gallery[[:>:]]'
OR events.description REGEXP '[[:<:]]Art Gallery[[:>:]]'
OR events.locationname REGEXP '[[:<:]]Art Gallery[[:>:]]'
OR events.contactname REGEXP '[[:<:]]Art Gallery[[:>:]]'
) OR (
events.name REGEXP '[[:<:]]Art[[:>:]]'
OR events.description REGEXP '[[:<:]]Art[[:>:]]'
OR events.locationname REGEXP '[[:<:]]Art[[:>:]]'
OR events.contactname REGEXP '[[:<:]]Art[[:>:]]'
) OR (
events.name REGEXP '[[:<:]]Gallery[[:>:]]'
OR events.description REGEXP '[[:<:]]Gallery[[:>:]]'
OR events.locationname REGEXP '[[:<:]]Gallery[[:>:]]'
OR events.contactname REGEXP '[[:<:]]Gallery[[:>:]]'
)
) AND (
events.price1 >= '0.00'
OR events.price2 >= '0.00'
OR events.price3 >= '0.00'
OR events.price4 >= '0.00'
) AND (
events.price1 <= '99.99'
OR events.price2 <= '99.99'
OR events.price3 <= '99.99'
OR events.price4 <= '99.99'
)
AND events.ages = '1'
AND events.category = '8'
AND event_city.region = '1'
AND event_dates.startdate >= '2002-01-1'
AND event_dates.enddate <= '2005-12-31'
AND event_dates.event_id = events.event_id
AND events.locationcity = event_city.city_id
AND events.active = '1';

There are 2 things I'm concerned with.

1) does it make sense to do the first set of regexp with both keywords? I was doing this in hopes that exact matches would be listed first, but it doesn't seem to be working that way.

2) is there a better way of doing this? the server this is on tends to be sluggish, and so does this query, is there any suggestions you might have to optimize this a bit. This is the longest query possible for the results page, if someone doesn't fill out a field in the advanced search, it's not included in the query.

http://dev.thompsonokanagan.com/trellis/events_advanced

well, this is where i'm lacking ... the only suggestion that i can make without thinking too hard is that you split it up into two queries ... do the first search for both keywords ... if nothing turns up, then do the search with the key words seperated ... other than that ...

Hmmm, can't you do all this more easily using JOIN's? Wouldn't that make the query smaller?

Also, since this query looks big, you might as well use this: mysql_free_result (http://www.php.net/mysql_free_result). If you're using OOP in your code, I suppose you can have this in the "destructor" (Only PHP5 with ZendEngine2 supports actual destructors, you will have to simulate it in older versions).
I think that might help you a bit...hopefully :)

If you do find some optimization methods for this sort of thing, do let me know because I'm still learning SQL.

I was thinking I might search the result with php and create a new array with the matches on top, then I thought, I'll never get this done on time if I try that. At most I can't see a particular result being more than 10, so I'm just not gonna worry about it.

Using JOIN wouldn't really do a whole lot different than the joins that I'm already doing.

The number of results being returned is low, thats why the big query, so free_result wouldn't really be necessary.

Thanks for your guy's input. I appreciate it.

yea, your right ... i think the only way this query will be faster (based on my knowledge) is by adding the appropriate indexes to the table on certain columns ... and, if some of the columns are varchar and can be set to unique ... that would help too.

Indexes, right. Would you recommend an index on each column? For example would it make sense to create an index on all 4 price columns, or an index on each individually. I think individually, but I'm not to sure.

if all (or a large majority) of your searches are querying all 4 columns all the time, then yes, index all 4.

If I create and index on all four, and an index on them individually, MySQL should use whichever is best for the current query, right?

Found this for the keyword search, but it seams that to get the desired results, I will need >= MySQL 4. Why is it that no servers have version 4? Everytime I find something cool to do in mysql, it requires 4.

http://www.databasejournal.com/features/mysql/article.php/1587371

MySQL doesn't ALWAYS know what to do ... sometimes you have to tell it and sometimes having too many indexes will slow it down ...

As for MySQL 4, it's not a stable build w/ a lot of the control panels out there like Ensim, etc ... i've still upgraded the majority of the servers that I admin without any problems ... makes a huge difference, espcially w/ the forums like vB