Re: Exporting large data from mysql to html using php

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jason Pruim
lists@xxxxxxxxxxxxxxxxxxxx



On Oct 26, 2011, at 6:23 AM, Tommy Pham wrote:

> On Wed, Oct 26, 2011 at 1:40 AM, Lester Caine <lester@xxxxxxxxxxx> wrote:
> 
>> Tommy Pham wrote:
>> 
>>> I wonder ... The real question is what's the purpose of the DB?  Is it for
>>> OLAP
>>> or OLTP? ;)
>>> As for dealing with DB having millions of rows, you're crossing over into
>>> DBA area.
>>> 
>> 
>> Many of my customers have coming up on 20 years of data available. There
>> has been a debate on transferring historic data to a separate database, but
>> having it available is not causing a problem, except for some counts and
>> larger search actions, and being able to see how long a client has been
>> visiting is often useful. Statistical analysis is always done on a separate
>> machine, with a replicated copy of the data, so as not to affect the active
>> users ...
>> 
>> 
> What kind of counts/filters?  What kind of RAID subsystem is the storage?
> What's the total size of the DB?  Up to 20 years of data should be in the
> peta range.  In that peta range, if you're not having performance issue and
> not using either RAID 0, 0+1, 10, 50, or 60, I'd love to hear about the
> application and database design in details. :)
> 
> 
>> That said, we are now using the much more detailed LLPG address data rather
>> than simple postcode, and that has added another order of magnitude to data
>> that is being searched live ...
>> 
>> 
>> --
>> Lester Caine - G8HFL
>> -----------------------------
>> Contact - http://lsces.co.uk/wiki/?page=**contact<http://lsces.co.uk/wiki/?page=contact>
>> L.S.Caine Electronic Services - http://lsces.co.uk
>> EnquirySolve - http://enquirysolve.com/
>> Model Engineers Digital Workshop - http://medw.co.uk//
>> Firebird - http://www.firebirdsql.org/**index.php<http://www.firebirdsql.org/index.php>
>> 
>> 
> Since Jason didn't disclose sufficient information, I suggested that RAID
> storage based on the worst case scenario being this:  as a business analyst,
> I'd want to do drill-downs, filters, counts of a the following for an
> up-coming marketing campaign:
> 
> * county(ies)
> * city(ies)
> * zip code(s)
> * address type (business and/or residential)
> * of business (commercial, industrial/manufacturing, etc)
> * of residential ( homes - single/townhouses - or apartments/condos )
> 
> The filters and counts will any combinations of the above, ie: 5 random zip
> codes within the given state that lists all other criteria and break down.
> As Jason mention having 89million rows for a given state, how long would it
> take to run the drill-downs if the DB isn't sitting on a fast storage
> medium?  That 89 million is the most likely the average count in the USA.
> For California and New York, the number can double that easily.  That's only
> the basic filtering.  What of a specific business industry such as
> landscaping?  What of the filtering by yearly income and/or real estate
> value?  BTW, as a business analyst, I don't want to wait hours for the info
> to update every time I change a criteria/filter to get the counts before I
> look into a few random individual records from the results.

The server that's running it is a home computer with a VPS installed... It's not my dev environment :)  

The information being searched is specifically phone numbers, and the bosses want to provide the public away to browse them, hence the pagination... Once I removed a COUNT from mysql it started working alot better... So I still need to provide a better pagination system, but it's working.

Oh, and the 89 million is just for one state :) We are talking the possibility of I believe 10 billion numbers to cover the US, not to mention Canada which I believe uses the same numbering system as we do so that could be another 10 billion... 

> 
> As I've mentioned, something of this magnitude is better to leave it to the
> DBA and work together with that DBA.  Either hire/contract one or become one
> :)

I'm working on becoming one ontop of web designer and programmer :)
> 
> 
> Regards,
> Tommy


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux