Re: Exporting large data from mysql to html using php

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