I made a little test with two query (one to obtain number of records and
other to obtain records) and
with one query (optimized for mysql)
The results are:
SELECT * FROM aiki_widgets WHERE widget_site='default'
2 query: 0.00340509414673 ms (157 records)
1 query: 0.0059449672699 ms( 157 records)
SELECT * FROM ocal_files WHERE user_name='rejon'
2 query: 0.00694608688354 ms (1296 records)
1 query: 0.0166521072388 (1296 records)
SELECT * FROM ocal_files WHERE user_name<>'rejon'
2 query: 0.0886828899384 (35220 records)
1 query: 1.23628592491 (35220 records)
SELECT * FROM ocal_files ORDER BY file_num_download DESC
2 query: 0.00303912162781 (36516 records)
1 query: 1.62598991394 (36516 records)
Conclusions:
"2 query" is faster than "1 query"after (who have cache benefit).
Instead "2 queries" can be faster in some case where (we lost some ms ,
with "2 queries" we can win half or 1 seconds.. per query.
Code:
<?php
mysql_connect("localhost","openclipart","aiki") or die("no conection");
mysql_select_db("openclipart") or die("no db");
$tests= array(
"SELECT * FROM aiki_widgets WHERE widget_site='default'",
"SELECT * FROM ocal_files WHERE user_name='rejon'" ,
"SELECT * FROM ocal_files WHERE user_name<>'rejon'",
"SELECT * FROM ocal_files ORDER BY file_num_download DESC" );
foreach ($tests as $test) {
echo "<p>$test",two_query($test), one_query($test),"</p>" ;
}
function two_query($SQL){
$regs= array();
$start= microtime(true);
$rs= mysql_query ( str_replace("*", "count(*)", $SQL) );
$total = mysql_fetch_array($rs);
$total = $total[0];
$rs= mysql_query ( $SQL . " LIMIT 2,30");
while ( $row = mysql_fetch_object($rs) ){
$regs[] = $row;
}
echo "<br>2 query: ",microtime(true)-$start, " ($total records)";
}
function one_query($SQL){
$regs= array();
$start= microtime(true);
$counter=0 ;
$rs = mysql_query ($SQL);
mysql_data_seek($rs, 30);
while ( $row = mysql_fetch_object($rs) && $counter<30){
$regs[]= $row;
$counter++;
}
$total = mysql_num_rows($rs);
echo "<br>1 query: ",microtime(true)-$start, " ($total records)";
}
?>
--
You received this bug notification because you are a member of Aiki
Framework Developers, which is subscribed to aikiframework.
https://bugs.launchpad.net/bugs/736257
Title:
aiki always run a query to count the records, need different solution
Status in Aiki Framework:
Confirmed
Bug description:
in widgets.php code between 375 and 386 should be replaced
To manage notifications about this bug go to:
https://bugs.launchpad.net/aikiframework/+bug/736257/+subscriptions
_______________________________________________
Mailing list: https://launchpad.net/~aikiframework-devel
Post to : [email protected]
Unsubscribe : https://launchpad.net/~aikiframework-devel
More help : https://help.launchpad.net/ListHelp
