Re: Exporting large data from mysql to html using php

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!