[PHP-DB] Slow query
Hi all, Below is the query we are getting a slow response on ... 2seconds ... with 4 records we were hoping to get it much quicker. Can someone advise if i can optimise this? SELECT s.movie_id, m.movie_name, movie_shortname, image_name, image_extension, m.trailerAvail FROM sessions AS s LEFT JOIN movies_information AS mi ON mi.movie_id = s.movie_id LEFT JOIN movies AS m ON m.movie_id = s.movie_id WHERE DATE( date_time ) = ( DATE_ADD( NOW( ) , INTERVAL 21 DAY ) ) AND image_name != '' GROUP BY s.movie_id ORDER BY RAND( ) LIMIT 4; Showing rows 0 - 3 (4 total, Query took 2.4823 sec) Explain SQL returns this; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE mi ALL NULL NULL NULL NULL 104 Using where; Using temporary; Using filesort 1 SIMPLE s ALL NULL NULL NULL NULL 3690 Using where 1 SIMPLE m ALL NULL NULL NULL NULL 298 Thanks Steven -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Slow query
Steven Macintyre wrote: Hi all, Below is the query we are getting a slow response on ... 2seconds ... with 4 records we were hoping to get it much quicker. Can someone advise if i can optimise this? SELECT s.movie_id, m.movie_name, movie_shortname, image_name, image_extension, m.trailerAvail FROM sessions AS s LEFT JOIN movies_information AS mi ON mi.movie_id = s.movie_id LEFT JOIN movies AS m ON m.movie_id = s.movie_id WHERE DATE( date_time ) = ( DATE_ADD( NOW( ) , INTERVAL 21 DAY ) ) AND image_name != '' GROUP BY s.movie_id ORDER BY RAND( ) LIMIT 4; Do you have indexes on: movies_information(movie_id); movies(movie_id); sessions(movie_id); How many rows in each table? Even though you're only selecting 4 rows you are doing an order by rand() which has to look at *all* rows of the result set to randomize the results - AND you are grouping by the movie_id - both of which can kill performance. Why are they left joins? can a movie not have information, or can it not have a session? Left joins have to inspect all rows in each of the tables as well. Try something like this: select m.movie_id, m.movie_name, m.movie_shortname, from movies m inner join movies_information mi on (m.movie_id=mi.movie_id) where m.movie_id in ( select movie_id from sessions where date(date_time) = (date_add(now(), interval 21 day)) order by rand() limit 4 ); Only the sessions table needs to check the date and do the random ordering. Once you have 4 movie_id's to choose from, the rest should be pretty quick. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Slow query
On Wed, Jul 23, 2008 at 4:48 AM, Chris [EMAIL PROTECTED] wrote: Steven Macintyre wrote: Hi all, Below is the query we are getting a slow response on ... 2seconds ... with 4 records we were hoping to get it much quicker. Can someone advise if i can optimise this? SELECT s.movie_id, m.movie_name, movie_shortname, image_name, image_extension, m.trailerAvail FROM sessions AS s LEFT JOIN movies_information AS mi ON mi.movie_id = s.movie_id LEFT JOIN movies AS m ON m.movie_id = s.movie_id WHERE DATE( date_time ) = ( DATE_ADD( NOW( ) , INTERVAL 21 DAY ) ) AND image_name != '' GROUP BY s.movie_id ORDER BY RAND( ) LIMIT 4; Do you have indexes on: movies_information(movie_id); movies(movie_id); sessions(movie_id); How many rows in each table? Even though you're only selecting 4 rows you are doing an order by rand() which has to look at *all* rows of the result set to randomize the results - AND you are grouping by the movie_id - both of which can kill performance. Why are they left joins? can a movie not have information, or can it not have a session? Left joins have to inspect all rows in each of the tables as well. Try something like this: select m.movie_id, m.movie_name, m.movie_shortname, from movies m inner join movies_information mi on (m.movie_id=mi.movie_id) where m.movie_id in ( select movie_id from sessions where date(date_time) = (date_add(now(), interval 21 day)) order by rand() limit 4 ); Only the sessions table needs to check the date and do the random ordering. Once you have 4 movie_id's to choose from, the rest should be pretty quick. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php also try using EXPLAIN on the SQL to see how the optimizer is attempting to retrieve the data -- Bastien Cat, the other other white meat
Re: [PHP-DB] Slow Query
How many agents do you have? If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through) graeme. Wendell Frohwein wrote: First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein -- Experience is a good teacher, but she sends in terrific bills. Minna Antrim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Slow Query
Thank you Graeme. But unfortunately, there are a 700 + agents. This number keeps growing every day as well. I had a vision of another idea. I don't know how much time it will save. In the table that stores all the agents zipcodes that they cover, I added longitude and latitude to the table. So when my zip code radius function kicks in, it will find agents with there respective zip code. Before it would take the property zip, query the zipcodes table with over 42,000 zip codes, then put those in an array, then search for agents from there. The way I see it, I killed one step in the process. But at the time of me writing this letter I am still trying to get it to work. If I do, I will post up some numbers to see how much faster it is. But I am still looking for a better way to do it. Thanks once again to everyone. -Wendell Frohwein -Original Message- From: graeme [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 12:02 AM To: Wendell Frohwein Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Slow Query How many agents do you have? If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through) graeme. Wendell Frohwein wrote: First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein -- Experience is a good teacher, but she sends in terrific bills. Minna Antrim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Slow Query
Here is the structure of the zipcodes table CREATE TABLE `zipcodes` ( `zipcode` mediumint(5) unsigned zerofill NOT NULL default '0', `lon` varchar(8) NOT NULL default '', `lat` varchar(8) NOT NULL default '', PRIMARY KEY (`zipcode`), KEY `lon` (`lon`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; And here is an example of one of the inserts INSERT INTO `zipcodes` VALUES (00501, '73.0456', '40.8153'); I am retrieving this data from phpmyadmin 2.6.0-rc2 Thanks again. -Wendell Frohwein -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 12, 2005 6:12 PM To: [EMAIL PROTECTED]; php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query are there indeces on the tables columns? Also does your query use the IN (zip1, zip2, zipN) format or or do you query for each zip individually? The second is absolute killer on the db bastien From: Wendell Frohwein [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Slow Query Date: Wed, 12 Jan 2005 14:48:20 -0800 First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Slow Query
Hi Wendell, What you've proposed is not a bad solution. There is some initial work to set up the stuff for the existing agents, but if you define a limit to the range that the agent works in, then you can take the result stuff it into a comma delimited string and place that into the agent_zip_codes field...then the realtime time query is a simple IN (zip1, zip2, zipN) statement and should be faster Bastien From: Wendell Frohwein [EMAIL PROTECTED] To: 'graeme' [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Date: Thu, 13 Jan 2005 00:30:34 -0800 Thank you Graeme. But unfortunately, there are a 700 + agents. This number keeps growing every day as well. I had a vision of another idea. I don't know how much time it will save. In the table that stores all the agents zipcodes that they cover, I added longitude and latitude to the table. So when my zip code radius function kicks in, it will find agents with there respective zip code. Before it would take the property zip, query the zipcodes table with over 42,000 zip codes, then put those in an array, then search for agents from there. The way I see it, I killed one step in the process. But at the time of me writing this letter I am still trying to get it to work. If I do, I will post up some numbers to see how much faster it is. But I am still looking for a better way to do it. Thanks once again to everyone. -Wendell Frohwein -Original Message- From: graeme [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 12:02 AM To: Wendell Frohwein Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Slow Query How many agents do you have? If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through) graeme. Wendell Frohwein wrote: First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein
RE: [PHP-DB] Slow Query
Thank You Bastien. My newest Vision that I had about taking out a step in the process has failed. Mainly because of the script that determines the radius. Since I was going to go off of the agents zipcode coverage area (to save a step), and not the zipcode table containing 45,000 + entry's. If an order was placed by a client with lets say the zip code 90606, and there was no agent in the entire system that had this exact zip code in there coverage area, the radius code would error out and return no matches. Even though in the 2-5 mile radius of 90606 there is matching agents. It needs to see that first zip codes (90606) longitude and latitude to even begin the radius function. So now I was bumped again. I rewrote some of my code to be a little more streamlined. So I began testing orders: 50, 100, 200, and 400. The time it takes to assign orders to agents is really had to determine because it is based on how many agents and how many zipcodes in those agents coverage area. With 400 orders within a heavy populated area it takes as much time as 127 seconds to process. If there are fewer zip codes in an area such as mid east / west it takes as little time as 11 seconds to process. When I start the assignment of orders I open up a shell via ssh, monitor the system using the top command. Mysqld spikes between 55% and 99% cpu usage the entire time of the assignment process. Where it says User CPU usage its between 70% and 99% cpu usage. And finally where it says System CPU Usage, it rarely goes over 3% - 5%. The server is not mine, but one I manage for the company. I used dmesg to find out that the server this site is running on is a 700mhz celeron coppermine, 128MB of ram, 10gig western digital drive. So with this in mind, I think my problem behind slow queries is affected in other areas other then the script itself. Thank you all very much for all your help. -Wendell Frohwein -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 10:04 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Hi Wendell, What you've proposed is not a bad solution. There is some initial work to set up the stuff for the existing agents, but if you define a limit to the range that the agent works in, then you can take the result stuff it into a comma delimited string and place that into the agent_zip_codes field...then the realtime time query is a simple IN (zip1, zip2, zipN) statement and should be faster Bastien From: Wendell Frohwein [EMAIL PROTECTED] To: 'graeme' [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Date: Thu, 13 Jan 2005 00:30:34 -0800 Thank you Graeme. But unfortunately, there are a 700 + agents. This number keeps growing every day as well. I had a vision of another idea. I don't know how much time it will save. In the table that stores all the agents zipcodes that they cover, I added longitude and latitude to the table. So when my zip code radius function kicks in, it will find agents with there respective zip code. Before it would take the property zip, query the zipcodes table with over 42,000 zip codes, then put those in an array, then search for agents from there. The way I see it, I killed one step in the process. But at the time of me writing this letter I am still trying to get it to work. If I do, I will post up some numbers to see how much faster it is. But I am still looking for a better way to do it. Thanks once again to everyone. -Wendell Frohwein -Original Message- From: graeme [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 12:02 AM To: Wendell Frohwein Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Slow Query How many agents do you have? If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through) graeme. Wendell Frohwein wrote: First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order
RE: [PHP-DB] Slow Query
me thinks its time for a new machine ;-) Bastien From: Wendell Frohwein [EMAIL PROTECTED] To: 'Bastien Koert' [EMAIL PROTECTED],[EMAIL PROTECTED] CC: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Date: Thu, 13 Jan 2005 13:21:33 -0800 Thank You Bastien. My newest Vision that I had about taking out a step in the process has failed. Mainly because of the script that determines the radius. Since I was going to go off of the agents zipcode coverage area (to save a step), and not the zipcode table containing 45,000 + entry's. If an order was placed by a client with lets say the zip code 90606, and there was no agent in the entire system that had this exact zip code in there coverage area, the radius code would error out and return no matches. Even though in the 2-5 mile radius of 90606 there is matching agents. It needs to see that first zip codes (90606) longitude and latitude to even begin the radius function. So now I was bumped again. I rewrote some of my code to be a little more streamlined. So I began testing orders: 50, 100, 200, and 400. The time it takes to assign orders to agents is really had to determine because it is based on how many agents and how many zipcodes in those agents coverage area. With 400 orders within a heavy populated area it takes as much time as 127 seconds to process. If there are fewer zip codes in an area such as mid east / west it takes as little time as 11 seconds to process. When I start the assignment of orders I open up a shell via ssh, monitor the system using the top command. Mysqld spikes between 55% and 99% cpu usage the entire time of the assignment process. Where it says User CPU usage its between 70% and 99% cpu usage. And finally where it says System CPU Usage, it rarely goes over 3% - 5%. The server is not mine, but one I manage for the company. I used dmesg to find out that the server this site is running on is a 700mhz celeron coppermine, 128MB of ram, 10gig western digital drive. So with this in mind, I think my problem behind slow queries is affected in other areas other then the script itself. Thank you all very much for all your help. -Wendell Frohwein -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 10:04 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Hi Wendell, What you've proposed is not a bad solution. There is some initial work to set up the stuff for the existing agents, but if you define a limit to the range that the agent works in, then you can take the result stuff it into a comma delimited string and place that into the agent_zip_codes field...then the realtime time query is a simple IN (zip1, zip2, zipN) statement and should be faster Bastien From: Wendell Frohwein [EMAIL PROTECTED] To: 'graeme' [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: RE: [PHP-DB] Slow Query Date: Thu, 13 Jan 2005 00:30:34 -0800 Thank you Graeme. But unfortunately, there are a 700 + agents. This number keeps growing every day as well. I had a vision of another idea. I don't know how much time it will save. In the table that stores all the agents zipcodes that they cover, I added longitude and latitude to the table. So when my zip code radius function kicks in, it will find agents with there respective zip code. Before it would take the property zip, query the zipcodes table with over 42,000 zip codes, then put those in an array, then search for agents from there. The way I see it, I killed one step in the process. But at the time of me writing this letter I am still trying to get it to work. If I do, I will post up some numbers to see how much faster it is. But I am still looking for a better way to do it. Thanks once again to everyone. -Wendell Frohwein -Original Message- From: graeme [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 12:02 AM To: Wendell Frohwein Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Slow Query How many agents do you have? If the number of agents is small then you could set up a temp table which has a link to the agent, the client, and a calculation of the distance between the two. Then do your search on this temp table. Once you have the answer you want delete the records for this client. (that should allow for more than one client entering data at the same time, although you may want to properly think that scenario through) graeme. Wendell Frohwein wrote: First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code
[PHP-DB] Slow Query
First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein
Re: [PHP-DB] Slow Query
Wendell Frohwein wrote: snip So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. snip there was/is a thread going on at php-generals regarding zipcode-long/lat issues (you may have seen it). one of the things that came up is doing the joins on the tables in question (and/or doin the maths) is heavy work. I would imagine that the results for a given calculation (i.e. get postcodes with certain radius) stays the same, if they do then it may be an idea to do the calculation before hand and store the results in a way that its very fast to access e.g. writing out PHP arrays into files for later inclusion as required (possibly because new postcodes are occasionally added you may want that to trigger a recalculation - e.g. when a new postcode is added do the calculation then for all postcodes found do a recalculation for those as well!). so...It doesn't sound like you can get round the ammount of processing that needs to be done but maybe you can do it 'offline' and store the results for quick access when you need it? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Slow Query
are there indeces on the tables columns? Also does your query use the IN (zip1, zip2, zipN) format or or do you query for each zip individually? The second is absolute killer on the db bastien From: Wendell Frohwein [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Slow Query Date: Wed, 12 Jan 2005 14:48:20 -0800 First of all I would like to thank anyone who lends a hand in this matter. Here is what im working with. Redhat 9, PHP 5.0.2, Apache/2.0.50, MySQL 4.1.8-standard Binary Installation for pc-linux-i686, OpenSSL 0.9.7d, gcc version 3.2.2. PHP compiled with mysql, and mysqli capability. This is how the script works: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It searches the database for agents / appraisers with the zip code in question (property zip code) in there coverage area (zip codes they are willing to do appraisal work for). So when it finds a match, It adds that order to there potential order list. It then waits for the to accept or decline the order. If accepted the order becomes there's and alerts all the other agents / appraisers that this order has already been accepted. The do the work, send it back job is done. This works fine right now rather it be a single order placed by a client, or a spreadsheet imported to the system with 2000 orders or more. So now you're saying if it works, why are you here asking for help? Well instead of searching agents with the exact zip code match, I would like to search a radius of zip codes. The first search would be a 2 mile radius, the next search (incase the first did not return any result) would be 5 mile radius. I purchased some software (php / mysql sql files) that contains every zipcode in the united states along with a longitude and latitude for each zip code. So the zip code script provided with this software allows you to 1) enter a zip code and miles in radius, it will then spit out all the zip codes in that radius into an array. 2) you can give it 2 zip codes and it will tell you the distance in miles between the 2 (give or take). So I rewrote my script to do the follow: The client submits a property for an appraisal. Based on the appraisal type and property zip code, It does a radius search of 2 miles from the property zip. If no matches are found, it does a 5 mile radius search. For every zip code returned by each radius search, It searches the database for agents / appraisers with the zip code in question (zip codes from radius search) in there coverage. This is the most stressful part I am assuming. This works fine for 1 - 10 orders. But when I import 30 or more, the script pretty much hangs as well as mysql. I have to kill mysql with signal 9, start mysql it up again. Then all is back to normal. I would paste the code in here but it is really long and complicated. I was hoping on the based on the operation of the script, someone would suggest a better and faster way to search zip code radius, while matching the results to agents within the system. If I am asking for way to much time from someone, I apologize. If someone is really interested in helping me sort this out, I could send you code samples to see the process. -Wendell Frohwein -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
