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)

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.