MySQL's InnoDB engine
Moderator: General Moderators
MySQL's InnoDB engine
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 :)
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 :)
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?
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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
Here's a screenshot of the result. This is only 2 out of the 12 tables that were generated.

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?

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?
Last edited by wyred on Fri Mar 31, 2006 12:51 am, edited 1 time in total.
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);
}- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
try this: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
This isn't tested, but the basic logic is there.
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))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))