php-general Digest 3 Nov 2011 07:31:51 -0000 Issue 7550
Topics (messages 315581 through 315582):
Re: Exporting large data from mysql to html using php
315581 by: Jason Pruim
315582 by: Ashley Sheridan
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 ---
Jason Pruim
li...@pruimphotography.com
On Oct 31, 2011, at 7:52 PM, Ashley Sheridan wrote:
> On Mon, 2011-10-31 at 19:29 -0400, Jason Pruim wrote:
>>
>> Jason Pruim
>> li...@pruimphotography.com
>>
>>
>>
>> On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:
>>
>> > On 10/24/2011 5: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
>> >>
>> >
>> > Jason,
>> >
>> > How large a data set are you starting with? How many records in all.
>> >
>> > Will you show us your DB schema?
>>
>> Hey Jim,
>>
>> I am working with 89 Million records right now... Going to be expanding to a
>> much larger dataset as the site expands.
>>
>> Here is the main table that I am using:
>>
>> mysql> describe main;
>> +------------+-------------+------+-----+---------+----------------+
>> | Field | Type | Null | Key | Default | Extra |
>> +------------+-------------+------+-----+---------+----------------+
>> | areacode | int(3) | NO | MUL | NULL | |
>> | exchange | int(3) | NO | | NULL | |
>> | subscriber | char(4) | NO | | NULL | |
>> | id | int(11) | NO | PRI | NULL | auto_increment |
>> | state | varchar(20) | YES | | NULL | |
>> | config | text | YES | | NULL | |
>> +------------+-------------+------+-----+---------+----------------+
>>
>>
>>
>> config is just going to contain varius settings for commenting on records,
>> and future expansion. State will actually be the state spelled out.
>>
>> Thanks for taking a looking!
>>
>>
>
> I'd put the spelling of the state in another table and just include the
> reference to it in this table, it will save a lot on storage and it's easy to
> do a join to get it. That way, it's also much faster to look up entries by
> state, as a numerical index is quicker that a string index.
>
> On the subject of indexes, what other ones do you have apart from the primary
> key there?
>
> --
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
Hey Ash,
Sorry for the delay... Had a sick kid at home which kept me very busy and away
from the computer!
You think putting the spelling of the state in another table with some kind of
a reference code (such as 1, 2, 3, 4, etc etc ) would be faster then searching
based on some spelling of the name? I am using the state name in the URL that
I'm referencing... So something like: www.example.com/new-york/212/314 would
bring up the entire 10,000 records for 212-314-****
Trying to avoid the normal index.php?vars=blah&you=Blah type stuff...
Indexes... I'm still learning about those... I created a index based on the
areacode, and exchange fields... But more reading is needed to figure out just
how to use it properly :)
Thanks Ash!
--- End Message ---
--- Begin Message ---
On Wed, 2011-11-02 at 19:47 -0400, Jason Pruim wrote:
> Jason Pruim
> li...@pruimphotography.com
>
>
>
> On Oct 31, 2011, at 7:52 PM, Ashley Sheridan wrote:
>
> > On Mon, 2011-10-31 at 19:29 -0400, Jason Pruim wrote:
> >>
> >> Jason Pruim
> >> li...@pruimphotography.com
> >>
> >>
> >>
> >> On Oct 31, 2011, at 7:11 PM, Jim Lucas wrote:
> >>
> >> > On 10/24/2011 5: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
> >> >>
> >> >
> >> > Jason,
> >> >
> >> > How large a data set are you starting with? How many records in all.
> >> >
> >> > Will you show us your DB schema?
> >>
> >> Hey Jim,
> >>
> >> I am working with 89 Million records right now... Going to be expanding to
> >> a much larger dataset as the site expands.
> >>
> >> Here is the main table that I am using:
> >>
> >> mysql> describe main;
> >> +------------+-------------+------+-----+---------+----------------+
> >> | Field | Type | Null | Key | Default | Extra |
> >> +------------+-------------+------+-----+---------+----------------+
> >> | areacode | int(3) | NO | MUL | NULL | |
> >> | exchange | int(3) | NO | | NULL | |
> >> | subscriber | char(4) | NO | | NULL | |
> >> | id | int(11) | NO | PRI | NULL | auto_increment |
> >> | state | varchar(20) | YES | | NULL | |
> >> | config | text | YES | | NULL | |
> >> +------------+-------------+------+-----+---------+----------------+
> >>
> >>
> >>
> >> config is just going to contain varius settings for commenting on records,
> >> and future expansion. State will actually be the state spelled out.
> >>
> >> Thanks for taking a looking!
> >>
> >>
> >
> > I'd put the spelling of the state in another table and just include the
> > reference to it in this table, it will save a lot on storage and it's easy
> > to do a join to get it. That way, it's also much faster to look up entries
> > by state, as a numerical index is quicker that a string index.
> >
> > On the subject of indexes, what other ones do you have apart from the
> > primary key there?
> >
> > --
> > Thanks,
> > Ash
> > http://www.ashleysheridan.co.uk
> >
> >
>
> Hey Ash,
>
> Sorry for the delay... Had a sick kid at home which kept me very busy and
> away from the computer!
>
> You think putting the spelling of the state in another table with some kind
> of a reference code (such as 1, 2, 3, 4, etc etc ) would be faster then
> searching based on some spelling of the name? I am using the state name in
> the URL that I'm referencing... So something like:
> www.example.com/new-york/212/314 would bring up the entire 10,000 records for
> 212-314-****
>
> Trying to avoid the normal index.php?vars=blah&you=Blah type stuff...
>
> Indexes... I'm still learning about those... I created a index based on the
> areacode, and exchange fields... But more reading is needed to figure out
> just how to use it properly :)
>
> Thanks Ash!
>
>
>
>
>
You could still search based on the state name, but you could then use a
join or first search the states table for the ids of each matching state
and use an IN clause in the query against the phone numbers.
--
Thanks,
Ash
http://www.ashleysheridan.co.uk
--- End Message ---
