Re: [PHP] Exporting large data from mysql to html using php
On Mon, Oct 24, 2011 at 7:50 PM, Jason Pruim wrote: > I have about 89 million records in mysql... the initial load of the page > takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL > query's... But they just aren't going fast enough... > > What I would like to do, is pull the data out of MySQL and store it in the > HTML files, and then update the HTML files once a day/week/month... I can > figure most of it out... BUT... How do I automatically link to the individual > pages? Does this help? http://peeling.hubpages.com/hub/Generate_Static_HTML_Pages_From_A_Database -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: Exporting large data from mysql to html using php
Jason Pruim wrote: > Now that I've managed to list 3 separate programming languages and > somewhat tie it back into php here's the question... > > I have about 89 million records in mysql... the initial load of the page > takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL > query's... But they just aren't going fast enough... > > What I would like to do, is pull the data out of MySQL and store it in the > HTML files, and then update the HTML files once a day/week/month... I can > figure most of it out... BUT... How do I automatically link to the > individual pages? > > I have the site working when you pull it from MySQL... Just the load time > sucks... Any suggestions on where I can pull some more info from? :) > > Thanks in advance! > > > Jason Pruim > li...@pruimphotography.com Is it possible that you are attempting to display images? And if so, are you making the mistake of storing them "full size" and using the browser to resize them? That would have the potential to cause long load times. Cheers -- David Robley Give me some chocolate and no one gets hurt! Today is Pungenday, the 6th day of The Aftermath in the YOLD 3177. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
On 2011-10-24, at 11:14 PM, Jason Pruim wrote: > > Jason Pruim > li...@pruimphotography.com > > > > On Oct 24, 2011, at 9:20 PM, Bastien wrote: > >> >> >> On 2011-10-24, at 8:50 PM, Jason Pruim wrote: >> >>> Now that I've managed to list 3 separate programming languages and somewhat >>> tie it back into php here's the question... >>> >>> I have about 89 million records in mysql... the initial load of the page >>> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL >>> query's... But they just aren't going fast enough... >>> >>> What I would like to do, is pull the data out of MySQL and store it in the >>> HTML files, and then update the HTML files once a day/week/month... I can >>> figure most of it out... BUT... How do I automatically link to the >>> individual pages? >>> >>> I have the site working when you pull it from MySQL... Just the load time >>> sucks... Any suggestions on where I can pull some more info from? :) >>> >>> Thanks in advance! >>> >>> >>> Jason Pruim >>> li...@pruimphotography.com >>> >>> >>> >>> >>> -- >>> PHP General Mailing List (http://www.php.net/) >>> To unsubscribe, visit: http://www.php.net/unsub.php >>> >> >> That's a ton of data. So there are a couple of questions: >> >> 1. Is the data ordered in any way? > > Data is in numeric order in the database, but not specifing a ORDER BY on the > SELECT.. >> >> The issue is that you might need to regenerate the files if new data needs >> to be interspersed. > > Indexes are freshly done... I might need to tweak them and learn more about > them... Working on that right now! >> >> 2. Why is pagination slow? > > Pagination isn't all that slow... Just the initial load of the site... The > rest of it works fairly well... But the guys with the paychecks aren't going > for a 2-3 minute initial load time on a public website >> >> A LIMIT with an OFFSET should be very quick if the table is properly >> indexed. Is there any tuning that you can do to further filter the results? >> Say by date or some other criteria? Have you run an EXPLAIN plan on the db >> to show how the queries are being run? >> >> Other thoughts: >> >> - are your indexes up to date? Have you optimized those index to be clean, >> not fragmented, and therefore fast? >> > > Just generated the indexes so it should be completely perfect. > >> - can you organize the data by some criteria to better show the results? >> Like by date? Or by starting alpha character? Or by category? > > Trying to split up the data some... It's phone numbers... So I have the area > code, exchange, and the subscriber number stored in 3 separate fields... But > that doesn't seem any faster then when I had it all in one field > > I think I just need to rethink the design a little bit, and optimize the > entire app... But I think that will hit tomorrow. > > Thanks for your suggestions! >> >> Bastien Koert >> 905-904-0334 >> >> >> >> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > My preference would be to have that as one numeric field ( bigint ) and search on that. DBs like numbers and should be able to return the data very quickly As for the app start, can you present a tiny ( say 10 rows ) subset that you could read in from a cache? Perhaps the 10 most common searched on or last 10 used? You could cache it for an hour and then refresh it. Same thing applies if the start is like a dashboard with various reports. Cache out what you can and hit the DB for the bare minimum to keep people happy Then your app should be right quick to start Bastien Koert 905-904-0334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
On 2011-10-24, at 11:26 PM, "Jim Giner" wrote: > Yes - but - we're talking about a user-app that the OP is trying to provide > 89M records to. Sure - "some" users might have need of looking at even as > much as a million records IF they were researching something that needed it. > But - for the 'general' user of an app - I cannot see a need to be providing > even that much data. Think about it - you give the user 1M records, how > long is that user going to page thru the results? Let's say there are 20 > results on a page and it takes a mere (wow!) 2 seconds to scan thru them > looking for something apparently very obvious. That's 600 results viewed > per minute at best. Six hundred into a M is 1666 minutes which is 27+ > hours. Even at 1 second per page view time, it's still more time than in a > normal work day. And you want come up with a way to feed him 89M? > > The problem needs to be looked at differently - put together a > business-smart solution to finding the needle in this haystack of 89M > pieces of straw and only then apply technology to it. > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > I agree, Jim. Which is why I was asking a about tuning the queries. Jason already indicated he was using limits and offsets, so it's other avenues i was looking for. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
Yes - but - we're talking about a user-app that the OP is trying to provide 89M records to. Sure - "some" users might have need of looking at even as much as a million records IF they were researching something that needed it. But - for the 'general' user of an app - I cannot see a need to be providing even that much data. Think about it - you give the user 1M records, how long is that user going to page thru the results? Let's say there are 20 results on a page and it takes a mere (wow!) 2 seconds to scan thru them looking for something apparently very obvious. That's 600 results viewed per minute at best. Six hundred into a M is 1666 minutes which is 27+ hours. Even at 1 second per page view time, it's still more time than in a normal work day. And you want come up with a way to feed him 89M? The problem needs to be looked at differently - put together a business-smart solution to finding the needle in this haystack of 89M pieces of straw and only then apply technology to it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
Jason Pruim li...@pruimphotography.com On Oct 24, 2011, at 9:20 PM, Bastien wrote: > > > On 2011-10-24, at 8:50 PM, Jason Pruim wrote: > >> Now that I've managed to list 3 separate programming languages and somewhat >> tie it back into php here's the question... >> >> I have about 89 million records in mysql... the initial load of the page >> takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL >> query's... But they just aren't going fast enough... >> >> What I would like to do, is pull the data out of MySQL and store it in the >> HTML files, and then update the HTML files once a day/week/month... I can >> figure most of it out... BUT... How do I automatically link to the >> individual pages? >> >> I have the site working when you pull it from MySQL... Just the load time >> sucks... Any suggestions on where I can pull some more info from? :) >> >> Thanks in advance! >> >> >> Jason Pruim >> li...@pruimphotography.com >> >> >> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> > > That's a ton of data. So there are a couple of questions: > > 1. Is the data ordered in any way? Data is in numeric order in the database, but not specifing a ORDER BY on the SELECT.. > > The issue is that you might need to regenerate the files if new data needs to > be interspersed. Indexes are freshly done... I might need to tweak them and learn more about them... Working on that right now! > > 2. Why is pagination slow? Pagination isn't all that slow... Just the initial load of the site... The rest of it works fairly well... But the guys with the paychecks aren't going for a 2-3 minute initial load time on a public website > > A LIMIT with an OFFSET should be very quick if the table is properly indexed. > Is there any tuning that you can do to further filter the results? Say by > date or some other criteria? Have you run an EXPLAIN plan on the db to show > how the queries are being run? > > Other thoughts: > > - are your indexes up to date? Have you optimized those index to be clean, > not fragmented, and therefore fast? > Just generated the indexes so it should be completely perfect. > - can you organize the data by some criteria to better show the results? Like > by date? Or by starting alpha character? Or by category? Trying to split up the data some... It's phone numbers... So I have the area code, exchange, and the subscriber number stored in 3 separate fields... But that doesn't seem any faster then when I had it all in one field I think I just need to rethink the design a little bit, and optimize the entire app... But I think that will hit tomorrow. Thanks for your suggestions! > > Bastien Koert > 905-904-0334 > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
On Oct 24, 2011, at 10:44 PM, Jim Giner wrote: > Why would any user need to have access to 89M records? They don't need access to it to edit it... Just to be able to view it... Also, it will be expanding in the future to include alot more data. Jason Pruim li...@pruimphotography.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
On 2011-10-24, at 10:44 PM, "Jim Giner" wrote: > Why would any user need to have access to 89M records? > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > History or audit trail data? I can think of lots. I know of an app with a poorly designed system that easily gets over 240 million Bastien Koert 905-904-0334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
Why would any user need to have access to 89M records? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Geo IP Location help needed...
On Mon, Oct 24, 2011 at 7:03 PM, Tommy Pham wrote: > On Mon, Oct 24, 2011 at 6:36 PM, DealTek wrote: > > >> >> but maybe the db is old from - Geo IP Location? hmmm . how do I check? >> >> -- >> Thanks, >> Dave - DealTek >> deal...@gmail.com >> [db-11] >> >> >> > Dave, > > I don't Linux too well but it should be similar to FreeBSD in some ways. > You can download the latest updates from Maxmind [1]. See [2] for the info > regarding your current GeoIP DB installed/used on the system. > > Regards, > Tommy > > [1] http://www.maxmind.com/app/php > [2] http://php.net/function.geoip-db-get-all-info > Forgot to include list.
Re: [PHP] Geo IP Location help needed...
On Oct 24, 2011, at 6:23 PM, Bastien wrote: > > On 2011-10-24, at 9:07 PM, DealTek wrote: > > > Dave, > > If the IP is showing, could there be some left over debug in some function? > > If the IP is not in your list it could be anything from a new range for a > region to IP spoofing or some anonymizer or even an old DB > > Bastien Koert > 905-904-0334 Thanks Bastien, simple code on my part - so no debug stuff... The tester with the error was a friend on his home dsl and also on his smartphone (so no IP spoofing from him)... but maybe the db is old from - Geo IP Location? hmmm . how do I check? the link does not provide any contact info... http://us3.php.net/manual/en/book.geoip.php -- Thanks, Dave - DealTek deal...@gmail.com [db-11] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Geo IP Location help needed...
On 2011-10-24, at 9:07 PM, DealTek wrote: > newbie question! > > Hi all, > > So, I had - Geo IP Location - installed for me on my VPS server by a network > tech > > http://us3.php.net/manual/en/book.geoip.php > > > then I used: > > geoip_country_name_by_name(xxx) to display the country by name > > basically works ok - however a few people say that my page: > > 1- will display their remote ip with $_SERVER['REMOTE_ADDR']; > > 2- ! but for some - the country name is blank ! (defeats the whole > purpose) > > - I am not sure how: Geo IP Location actually works... > > Q: Am I correct in assuming that when a country name does NOT show - is it > because that particular IP is NOT seen by Geo IP Location? > > We just installed the latest version (I think) yesterday > > Is it reading from some old outdated database - or > > > > Q: Is there some code that can ALWAYS tell the country name of the IP? > > > > > -- > Thanks, > Dave - DealTek > deal...@gmail.com > [db-11] > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > Dave, If the IP is showing, could there be some left over debug in some function? If the IP is not in your list it could be anything from a new range for a region to IP spoofing or some anonymizer or even an old DB Bastien Koert 905-904-0334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
On 2011-10-24, at 8:50 PM, Jason Pruim wrote: > Now that I've managed to list 3 separate programming languages and somewhat > tie it back into php here's the question... > > I have about 89 million records in mysql... the initial load of the page > takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL > query's... But they just aren't going fast enough... > > What I would like to do, is pull the data out of MySQL and store it in the > HTML files, and then update the HTML files once a day/week/month... I can > figure most of it out... BUT... How do I automatically link to the individual > pages? > > I have the site working when you pull it from MySQL... Just the load time > sucks... Any suggestions on where I can pull some more info from? :) > > Thanks in advance! > > > Jason Pruim > li...@pruimphotography.com > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > That's a ton of data. So there are a couple of questions: 1. Is the data ordered in any way? The issue is that you might need to regenerate the files if new data needs to be interspersed. 2. Why is pagination slow? A LIMIT with an OFFSET should be very quick if the table is properly indexed. Is there any tuning that you can do to further filter the results? Say by date or some other criteria? Have you run an EXPLAIN plan on the db to show how the queries are being run? Other thoughts: - are your indexes up to date? Have you optimized those index to be clean, not fragmented, and therefore fast? - can you organize the data by some criteria to better show the results? Like by date? Or by starting alpha character? Or by category? Bastien Koert 905-904-0334 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Exporting large data from mysql to html using php
2-3 minutes is long enough. I think your SELECT query and MySQL schema has something that can be improved. Sent from a handheld device. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Geo IP Location help needed...
newbie question! Hi all, So, I had - Geo IP Location - installed for me on my VPS server by a network tech http://us3.php.net/manual/en/book.geoip.php then I used: geoip_country_name_by_name(xxx) to display the country by name basically works ok - however a few people say that my page: 1- will display their remote ip with $_SERVER['REMOTE_ADDR']; 2- ! but for some - the country name is blank ! (defeats the whole purpose) - I am not sure how: Geo IP Location actually works... Q: Am I correct in assuming that when a country name does NOT show - is it because that particular IP is NOT seen by Geo IP Location? We just installed the latest version (I think) yesterday Is it reading from some old outdated database - or Q: Is there some code that can ALWAYS tell the country name of the IP? -- Thanks, Dave - DealTek deal...@gmail.com [db-11] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Exporting large data from mysql to html using php
Now that I've managed to list 3 separate programming languages and somewhat tie it back into php here's the question... I have about 89 million records in mysql... the initial load of the page takes 2 to 3 minutes, I am using pagination, so I have LIMIT's on the SQL query's... But they just aren't going fast enough... What I would like to do, is pull the data out of MySQL and store it in the HTML files, and then update the HTML files once a day/week/month... I can figure most of it out... BUT... How do I automatically link to the individual pages? I have the site working when you pull it from MySQL... Just the load time sucks... Any suggestions on where I can pull some more info from? :) Thanks in advance! Jason Pruim li...@pruimphotography.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DEV] 5.4.0 beta2
Hi Stas: > The next release is planned to be RC1 and is scheduled for November 10. You want the DateTime DST fixing RFC (https://wiki.php.net/rfc/datetime_and_daylight_saving_time) implemented before RC1, right? If so, keep in mind that voting on it will start tomorrow and finish on November 1. Then "someone" needs to implement it. The deadline may need adjusting. Thanks, --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
