Jason Pruim
li...@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
>> li...@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
>> >> li...@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!





Reply via email to