Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Large database to database querying and storing estimated values

10:34 pm on Dec 15, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:June 16, 2011
posts: 79
votes: 0

Hi everyone,

Trying to figure out exactly how to implement this current project to derive estimates from 1 db to another and stores the estimated value in one of the databases. I currently have, what I think is a pretty hacked version of it and works but does not seemed optimized at all and is pretty darn slow...what i have currently is below:

I have 2 tables in my database: one for previously sold products (approx. 600,000 rows) and one for items not yet for sale (approx. 50,000 rows and growing).

What I would ideally like to do is present the site user with an estimated value/sale price of the items that are not currently for sale based on parameters matching/querying against the database for the previously sold items.

I currently have this working on the fly but since it has to query the database on every page load/item they visit it is quite time consuming in regards to page load time (though I’m sure my query could be optimized quite a bit…)

So, I was playing with the idea of possibly, weekly or bi-monthly, looping through the data and creating estimates for each item and storing the estimated value with in a column with a corresponding “last modified estimate” date to query against.

My problem is that I’m really not sure how to set something up w/ this size to make it as optimized as possible…

Example of how I am currently querying:

-search.php stores my class variables and search parameters and initiates the query and stores the "$count"
-calculate_item_value.php calculates, in php, the results and averages an estimated value base on query results

 $previous_start_time = file_get_contents("last-run");

$items = Items::getItems($previous_start_time);

foreach ($items as $item) {

//stores beginning search parameters to be inserted
//and used in queries
$percentSearch = 0.20;
$radius = 0.25;
$yearbuiltrange = 10;
$soldtime = '-4 months';

//checks if query results is 3 or more....
//if so use these items to calculate estimate
if ($count >= 3) {

//simple calculation in php from the 3 or
//more query items to derive value and
//also Updates Mysql row for estimate value

else {

//increase search parameter ranges
$percentSearch = 0.25;
$radius = 0.5;
$yearbuiltrange = 12;
$soldtime = '-8 months';


if ($count >= 3) {


else {

$percentSearch = 0.30;
$radius = 0.75;
$yearbuiltrange = 15;
$soldtime = '-2 years';


if ($count >= 3) {


else echo "Estimate Failed";



How do really large sites handle creating estimates or similar things on their sites and databases? This just seems very VERY slow and un-optimized. Have been thinking that I might be able to use 1 query w/ a broader set of criteria and just use PHP to calculate using the search parameter variables but haven't tried that yet...that is probably next on my list.

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members