Re: Exporting large data from mysql to html using php

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 2011-11-02 at 19:47 -0400, Jason Pruim wrote:

> Jason Pruim
> lists@xxxxxxxxxxxxxxxxxxxx
> 
> 
> 
> 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
> >> lists@xxxxxxxxxxxxxxxxxxxx
> >> 
> >> 
> >> 
> >> 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
> >> >> lists@xxxxxxxxxxxxxxxxxxxx
> >> >> 
> >> > 
> >> > 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



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux