Re: Mysql strategy

Larry Garfield escreveu:

On Monday 13 November 2006 17:51, Chris wrote:

It's not going to make a great deal of difference if you do the processing in the MySQL or the PHP, in this case it's basically the same operation in each. I suspect that efficiently recreating the LIKE functionality in PHP wouldn't be trivial to do, if you are just doing straight comparisons the MySQL STRCMP function should be faster.

I'd say there will be a big difference. Pulling in 10,000 entries from the database and then sorting them in php will take a lot of memory (and database time to retrieve all of the entries). Getting the database to restrict that number of entries will take a little time but it doesn't have to return all entries, your php memory won't blow out and it won't have bugs in it.

As a general rule, I try to push as much logic into the query as I can for the simple reason that MySQL is optimized C and my PHP code gets interpreted. The odds of me writing something in PHP that's faster than MySQL AB's C code are slim. :-) The exception is grouping, which I've often had to do in PHP with a loop to rebuild a result array. The performance hit for that is not that big, however, and if you free() the result set afterward then the memory usage is not a major issue either. If you're finding your query is slow, look into your indexes. Just today I cut a single query from 230 seconds to 21 seconds just by adding two indexes. :-) I´m buiding a system that perform searches based on comma-separated "tags". I´m using the MySQL FIND_IN_SET function.

:D