Page 1 of 1

MySQL's InnoDB engine

Posted: Thu Mar 30, 2006 9:12 pm
by wyred
I have a table with 460,000+ rows of data, takes up 83MB of disk space. And I have a php script that queries that table, a total of 144 SQL queries each using 2 SUM() and 1 COUNT().

Originally, this table is using the MyISAM engine. It initially takes 1 second to process a single query and the time taken to finish a query gradually increases to 2, 3 then full 7 seconds near the end. I assume this is because the memory on the server is used up? Is there anything I can do to fix this?

Next, I made a copy of this table and changed the engine to InnoDB. My queries are running faster this time. A stable 0.5-0.6 seconds taken to process each query. But I noticed the disk space used has increased to 210MB. Is this normal?

Thanks :)

Posted: Thu Mar 30, 2006 10:38 pm
by feyd
free the results after you are done with them.

Posted: Thu Mar 30, 2006 10:51 pm
by wyred
I read up on mysql_free_result() and it seems to be only useful for queries that return huge result sets meaning hundreds-thousands of rows? (Forgive me if I'm wrong)

My SQL query is as following:
SELECT COUNT(revenue) AS revenue_count, SUM(occupied) AS occupied_sum, SUM(revenue) AS revenue_sum FROM `transactions` WHERE property='$product_code' AND rptdate >= '$date_start' AND rptdate <= '$date_end'

The returned result is only 1 row of data. Is mysql_free_result() still useful here?

Posted: Thu Mar 30, 2006 11:07 pm
by feyd
are all the calls similar to this query? If so, you can often combine them into a single query (that's barely more than what you already have.)

Posted: Thu Mar 30, 2006 11:35 pm
by wyred
Yup, all 144 SQL queries are the same, only the variables change.

How can they be combined?

Posted: Thu Mar 30, 2006 11:44 pm
by feyd
that depends on how they change. If only $product_code changes, it could be a matter of moving the "property" field to a group by and using it in the where to only filter to the set of product codes you want to keep. If the dates shift around, I wouldn't recommend a group by route, but maybe a union route.

Posted: Fri Mar 31, 2006 12:19 am
by wyred
Here's a screenshot of the result. This is only 2 out of the 12 tables that were generated.

Image
Ignore "No. of Occupied", "Days" and "Occupancy". No. of records are how much rows of data that fit the criteria.

A single row of data looks like:
2003-02-23(date) 145.24(revenue) and other info.

As shown, it took 39 seconds to fetch those data. Is this normal for a MyISAM engine?

Posted: Fri Mar 31, 2006 12:31 am
by feyd
Can you post the code of how your run through the queries and how the variables used in the query vary from query to query?

Posted: Fri Mar 31, 2006 12:41 am
by wyred
Sure~

Code: Select all

$product = array('PROD_A'=>'Product A','PROD_B'=>'Product B');
	$month_names = array('1'=>'Jan','2'=>'Feb','3'=>'Mar','4'=>'Apr','5'=>'May','6'=>'Jun','7'=>'Jul','8'=>'Aug','9'=>'Sep','10'=>'Oct','11'=>'Nov','12'=>'Dec');
	$year = array(2001,2002,2003,2004,2005,2006);
	$total_num_rooms = 251;

	foreach($product as $product_code=>$product_name) {
		echo "<div class=\"product\"><b>$product_name</b><br />";
		echo '<table border="0" cellspacing="0" cellpadding="5">';
		$first_table = true;
		foreach($year as $keyyear=>$valueyear) {
			echo ($first_table)?'<tr><td>':'<td>';
			$table_time_start = microtime();
			$sum_revenue_count = 0;
			$sum_occupied_sum = 0;
			$sum_revenue_sum = 0;
			$sum_total_num_days = 0;
			$sum_occupancy = 0;
			echo '<table border="1" cellspacing="0" cellpadding="5" class="outline_table">';
			echo '<tr><th class="outline_td">'.$valueyear.'</th><th class="outline_td">No. of<br />Records</th><th class="outline_td">No. of<br />Occupied</th><th class="outline_td">Rev. Amount</th><th class="outline_td">Days</th><th class="outline_td">Occupancy</th></tr>';
			for ($month=1;$month<=12;$month++) {
				$days_in_month = strftime('%d',mktime(0, 0, 0, $month+1, 0, $valueyear));
				$p_month = ($month<10)?'0'.$month:$month;
				$date_start = $valueyear.'-'.$p_month.'-01';
				$date_end = $valueyear.'-'.$p_month.'-31';
				$sql = "SELECT count(revenue) AS revenue_count, sum(occupied) AS occupied_sum, sum(revenue) AS revenue_sum FROM `tbl_dailytransactions` WHERE property='$product_code' AND rptdate >= '$date_start' AND rptdate <= '$date_end'";
				$row = db_query_single($sql);
				echo '<tr>';
				echo '<td class="outline_td" align="right">'.$month_names[$month].'</td>';
				echo '<td class="outline_td" align="right">'.number_format($row['revenue_count']).'</td>';
				echo '<td class="outline_td" align="right">'.number_format($row['occupied_sum']).'</td>';
				echo '<td class="outline_td" align="right">'.number_format($row['revenue_sum'],2).'</td>';
				echo '<td class="outline_td" align="right">'.$days_in_month.'</td>';
				$occupancy = (($row['occupied_sum']/($total_num_rooms*$days_in_month))*100);
				echo '<td class="outline_td" align="right">'.number_format($occupancy,2).'%</td>';
				echo '</tr>';
				$sum_revenue_count += $row['revenue_count'];
				$sum_occupied_sum += $row['occupied_sum'];
				$sum_revenue_sum += $row['revenue_sum'];
				$sum_total_num_days += $days_in_month;
				$sum_occupancy += $occupancy;
			}
			echo '<tr bgcolor="#FFEEEE">';
			echo '<td class="outline_td"><b>Total:</b></td>';
			echo '<td class="outline_td" align="right">'.number_format($sum_revenue_count).'</td>';
			echo '<td class="outline_td" align="right">'.number_format($sum_occupied_sum).'</td>';
			echo '<td class="outline_td" align="right">'.number_format($sum_revenue_sum,2).'</td>';
			echo '<td class="outline_td" align="right">'.$sum_total_num_days.'</td>';
			echo '<td class="outline_td" align="right">'.number_format(($sum_occupancy/12),2).'%<br /><span style="font-size:10px;">average</span></td>';
			echo '</tr>';
			echo '<tr><td colspan="6" align="right" class="outline_td" style="font-size:10px;color:#666666;">Processing time for this table: <b>'.getElapsedTime($table_time_start).'</b> seconds.</td></td>';
			echo '</table>';

			echo ($first_table)?'</td>':'</td></tr>';
			$first_table = !$first_table;
		}
		if (!$first_table) echo '</td></tr>';
		echo '</table></div>';
	}
	
	function getElapsedTime($start)	{
		$_NOW = microtime();
		list($usec_s,$sec_s)=explode(' ',$start);
		list($usec_n,$sec_n)=explode(' ',$_NOW);
		$reftime = (round(time() / 100) * 100) + 100;
		$sec_s -= $reftime;
		$sec_n -= $reftime;
		return ((float)$sec_n + (float)$usec_n) - ((float)$sec_s + (float)$usec_s);
	}
	
	function db_query_single($sql) {
		global $sql_queries;
		$timestart = microtime();
		$rs = mysql_query($sql) or die(mysql_error());
		$total_runtime = getElapsedTime($timestart);
		$sql_queries[] = array('sql'=>$sql, 'time'=>$total_runtime.' secs');
		return mysql_fetch_assoc($rs);
	}

Posted: Fri Mar 31, 2006 12:51 am
by feyd
try this:

Code: Select all

SELECT YEAR(rptdate) as `year`, MONTH(rptdate) as `month`, count(revenue) AS revenue_count, sum(occupied) AS occupied_sum, sum(revenue) AS revenue_sum FROM `tbl_dailytransactions` WHERE property = '$product_code' GROUP BY CONCAT(YEAR(rptdate), '-', MONTH(rptdate))
you'll have to make adjustments to your code to expect multiple records in a single query. It's even possible to get away from looping the products as well

Code: Select all

$products = '\'' . implode('\', \'', array_keys($product)) . '\'';

Code: Select all

SELECT YEAR(rptdate) as `year`, MONTH(rptdate) as `month`, count(revenue) AS revenue_count, sum(occupied) AS occupied_sum, sum(revenue) AS revenue_sum FROM `tbl_dailytransactions` WHERE property IN ($products) GROUP BY property, CONCAT(YEAR(rptdate), '-', MONTH(rptdate))
This isn't tested, but the basic logic is there.

Posted: Fri Mar 31, 2006 1:16 am
by wyred
I ran it in phpmyadmin and it took 3 seconds to generate all those data! It's a lot faster now unless it's cached results which I don't think it is.

Thanks Feyd!