Re: Exporting large data from mysql to html using php

On Wed, 2011-11-02 at 19:47 -0400, Jason Pruim wrote: > Jason Pruim > lists@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 > >> lists@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 > >> >> lists@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