On 14/11/06, Larry Garfield <larry@xxxxxxxxxxxxxxxx> wrote:
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. :-)
Alright, what I did was: 1) Parse the text for all the possible matches in the database. Each occurrence I store in an array, and replaced the occurrence with [n] where n=0,1,2,... 2) Performed a single "SELECT field1, field2 FROM table WHERE col1 LIKE '%$occurrence[0]%' OR WHERE col1 LIKE '%$occurrence[1]%' OR......... 3) Matched each [n] against the rows returned. 4) Replaced each [n] with whatever matched. The script does not seem to be too slow, and although it's heavy on the php arrays, it's letting the database do the heavy lifting. Ugly, but it works. Thanks for everybody's help. Dotan Cohen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
