php-general Digest 25 Oct 2011 12:25:34 -0000 Issue 7536
Topics (messages 315402 through 315418):
Exporting large data from mysql to html using php
315402 by: Jason Pruim
315404 by: Shiplu Mokaddim
315405 by: Bastien
315409 by: Jim Giner
315410 by: Bastien
315411 by: Jason Pruim
315412 by: Jason Pruim
315413 by: Jim Giner
315414 by: Bastien
315415 by: Bastien
315416 by: David Robley
315417 by: tamouse mailing lists
Geo IP Location help needed...
315403 by: DealTek
315406 by: Bastien
315407 by: DealTek
315408 by: Tommy Pham
315418 by: Tim Streater
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
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.
--- End Message ---
--- Begin Message ---
On 2011-10-24, at 8:50 PM, Jason Pruim <li...@pruimphotography.com> 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
--- End Message ---
--- Begin Message ---
Why would any user need to have access to 89M records?
--- End Message ---
--- Begin Message ---
On 2011-10-24, at 10:44 PM, "Jim Giner" <jim.gi...@albanyhandball.com> 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
--- End Message ---
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
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 <li...@pruimphotography.com> 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
>
--- End Message ---
--- Begin Message ---
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.
--- End Message ---
--- Begin Message ---
On 2011-10-24, at 11:26 PM, "Jim Giner" <jim.gi...@albanyhandball.com> 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.
--- End Message ---
--- Begin Message ---
On 2011-10-24, at 11:14 PM, Jason Pruim <li...@pruimphotography.com> 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 <li...@pruimphotography.com> 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
--- End Message ---
--- Begin Message ---
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.
--- End Message ---
--- Begin Message ---
On Mon, Oct 24, 2011 at 7:50 PM, Jason Pruim <li...@pruimphotography.com> 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
--- End Message ---
--- Begin Message ---
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]
--- End Message ---
--- Begin Message ---
On 2011-10-24, at 9:07 PM, DealTek <deal...@gmail.com> 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
--- End Message ---
--- Begin Message ---
On Oct 24, 2011, at 6:23 PM, Bastien wrote:
>
> On 2011-10-24, at 9:07 PM, DealTek <deal...@gmail.com> 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...
<?php
$ip = $_SERVER['REMOTE_ADDR'];
$this = geoip_country_name_by_name($ip);
echo 'The country you are in is : '.$this;
?>
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]
--- End Message ---
--- Begin Message ---
On Mon, Oct 24, 2011 at 7:03 PM, Tommy Pham <tommy...@gmail.com> wrote:
> On Mon, Oct 24, 2011 at 6:36 PM, DealTek <deal...@gmail.com> 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.
--- End Message ---
--- Begin Message ---
On 25 Oct 2011 at 02:36, DealTek <deal...@gmail.com> wrote:
> On Oct 24, 2011, at 6:23 PM, Bastien wrote:
>
>> On 2011-10-24, at 9:07 PM, DealTek <deal...@gmail.com> wrote:
>> 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
> simple code on my part - so no debug stuff...
>
> <?php
> $ip = $_SERVER['REMOTE_ADDR'];
> $this = geoip_country_name_by_name($ip);
> echo 'The country you are in is : '.$this;
> ?>
>
> 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
You can do a test yourself by hand. Go to www.ripe.net (one of the registries
that allocates IP addresses). Click where it says: Ripe database. In the Search
field type your IP address. Under Sources click on All. Under Types click on
inetnum. Under Flags click on B (shows full details). Then click on Search, and
scroll down to look at the results. You need to look at the inetnum object that
contains the IP address of interest, then see Country.
Be aware that what this tells you is where an IP block is registered. Nothing
to stop the entity using it from using those addresses anywhere on the planet,
if it has its own network.
--
Cheers -- Tim
--- End Message ---
