Re: Exporting large data from mysql to html using php

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!