Re: [PHP] Mysql strategy
On 21/11/06, Richard Lynch [EMAIL PROTECTED] wrote: On Sun, November 12, 2006 6:02 pm, Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? There are about 500 rows in the table, and growing. The mysql server is on localhost, and the machine is a dedicated server running RHEL. It's not my machine, so I'd like to be responsible with resources, as much as I can be. Thanks in advance for any advice. I think what you have done is very similar to the full text index solution of MySQL, only yours is a lot slower... You may want to re-read about MySQL full text index: http://mysql.com/ Thanks, Richard. I'm looking into the full text index again. Dotan Cohen http://dotancohen.com/ http://lyricslist.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
At 1:50 PM +0200 11/22/06, Dotan Cohen wrote: Thanks, Richard. I'm looking into the full text index again. Dotan Cohen Dotan: The following is a great reference -- the code works and it gave me the basics to do full-text searches. http://www.phpfreaks.com/tutorials/129/0.php Go though that, and I'm sure you'll find what you need. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
On 22/11/06, tedd [EMAIL PROTECTED] wrote: At 1:50 PM +0200 11/22/06, Dotan Cohen wrote: Thanks, Richard. I'm looking into the full text index again. Dotan Cohen Dotan: The following is a great reference -- the code works and it gave me the basics to do full-text searches. http://www.phpfreaks.com/tutorials/129/0.php Go though that, and I'm sure you'll find what you need. Cheers, tedd Thanks, Tedd. That's exactly the type of tutorial that I've been looking for. I'm already a member of phpfreaks, too, though I haven't visited the site in quite a while. Dotan Cohen http://what-is-what.com/what_is/web_browser.html http://lyricslist.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
Chris wrote: David Tulloh wrote: Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? 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. Yes, of course, much better to run it in the database, my bad. I was concentrating far too much on the processing complexity. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
On 14/11/06, Larry Garfield [EMAIL PROTECTED] 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
Re: [PHP] Mysql strategy
On 13/11/06, Chris [EMAIL PROTECTED] wrote: Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword No. Horribly inefficient. 2) To select the varchar field from all the rows, and search through them with php's array functions? No. Make the database do the work. Use full text indexing: http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html Thanks, Chris, but that approach won't work as I don't know all the values that I'll be searching for at any one time. In fact, I only know one of them at a time. This is the makeLink function in another thread that you helped me in. However, I do see other use of the full-text search functions in my future! This looks like it could save me quite a bit of work on another project. Dotan Cohen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
On 13/11/06, David Tulloh [EMAIL PROTECTED] wrote: Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? 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. If you are worried about the speed of this query I'd suggest rethinking your database structure. Text field comparisons will always be relatively slow compared to numeric comparisons or numeric lookups. My reading of this query however is that it should be 30 OR comparisons, no joins involved. In this case the query will scale linearly with your database size so you shouldn't worry too much about it slowing down over time. By that do you mean that it is O=n? Yes, it is linear. It's a matter of knowing if 30 LIKE queries are more efficient than one big query that brings down three fields (an INT and two VARCHAR's) for every row in the table. Now that I'm thinking about it, I'm probably better off with one big query, as it is only one query, and I'd imagine that flipping through an array 30 times is faster than making 30 database calls. Dotan Cohen http://what-is-what.com/what_is/digg.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? 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. If you are worried about the speed of this query I'd suggest rethinking your database structure. Text field comparisons will always be relatively slow compared to numeric comparisons or numeric lookups. My reading of this query however is that it should be 30 OR comparisons, no joins involved. In this case the query will scale linearly with your database size so you shouldn't worry too much about it slowing down over time. David -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
On Tue, 2006-11-14 at 00:51 +1100, David Tulloh wrote: Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? 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. If you are worried about the speed of this query I'd suggest rethinking your database structure. Text field comparisons will always be relatively slow compared to numeric comparisons or numeric lookups. My reading of this query however is that it should be 30 OR comparisons, no Except that most search engines refine the search when you add more keywords in contrast to your suggestion of expanding the search :) Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
David Tulloh wrote: Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? 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. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
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. :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql strategy
If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword 2) To select the varchar field from all the rows, and search through them with php's array functions? There are about 500 rows in the table, and growing. The mysql server is on localhost, and the machine is a dedicated server running RHEL. It's not my machine, so I'd like to be responsible with resources, as much as I can be. Thanks in advance for any advice. Dotan Cohen http://what-is-what.com/what_is/ajax.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql strategy
Dotan Cohen wrote: If I have to perform 30 LIKE searches for different keywords in a varchar field, which strategy would be recommended: 1) 30 searches, one for each keyword No. Horribly inefficient. 2) To select the varchar field from all the rows, and search through them with php's array functions? No. Make the database do the work. Use full text indexing: http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
