Doc #52127 [Com]: mysl_fetch_assoc is much slower than mysql_fetch_row
| From: | tocker at gmail dot com | Date: | Wed, 28 Jul 2010 00:18:23 +0000 |
| Subject: | Doc #52127 [Com]: mysl_fetch_assoc is much slower than mysql_fetch_row | ||
| References: | 1 | Groups: | php.doc.bugs |
| Request: | Send a blank email to doc-bugs+get-4759@lists.php.net to get a copy of this message | ||
Edit report at http://bugs.php.net/bug.php?id=52127&edit=1
ID: 52127
Comment by: tocker at gmail dot com
Reported by: skyeye at o2 dot pl
Summary: mysl_fetch_assoc is much slower than mysql_fetch_row
Status: Assigned
Type: Documentation Problem
Package: *General Issues
Operating System: CentOS
PHP Version: 5.3.2
Assigned To: preinheimer
Block user comment: N
New Comment:
I don't think the docs should be changed. This is worst-case analysis,
and performance is only measurably different because the reporter is
extracting too many rows. In the typical case I would never recommend
someone grab greater than 100 in one go, and in that case the round trip
cost is much more visible as being a large part of the cost.
If there's any docs correction to be made - it should be that 90K rows
at a time is excessive, and not a best practice.
Previous Comments:
------------------------------------------------------------------------
[2010-06-21 22:40:29] skyeye at o2 dot pl
Obviusly there should be:
while($object = mysql_fetch_object($result))
{
}
instead of:
while($object = mysql_fetch_row($result))
{
}
but it is not so important as we mainly compare mysql_fetch_assoc() with
mysql_fetch_row().
------------------------------------------------------------------------
[2010-06-21 22:33:17] skyeye at o2 dot pl
With this code (please test it for any database that has large number of
records and columns):
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
echo "<font color='green'>1. mysql_fetch_assoc() +
array</b></font>";
$link = mysql_connect(HOST,USER,PASS);
mysql_select_db(DB,$link);
$result = mysql_query("SELECT ... FROM ... ORDER BY '...' DESC LIMIT
300");
mysql_close($link);
$start = microtime_float();
while($tab1 = mysql_fetch_assoc($result))
{
}
$stop = microtime_float();
echo "<BR><BR>".($stop - $start)." [s].";
echo "<BR><BR><font color='green'>2. mysql_fetch_row +
array</b></font>";
$start = microtime_float();
while($tab2 = mysql_fetch_row($result))
{
}
$stop = microtime_float();
echo "<BR><BR>".($stop - $start)." [s].";
echo "<BR><BR><font color='green'>3. mysql_fetch_row +
list()</b></font>";
$start = microtime_float();
while(list([variables for all 42 columns]) = mysql_fetch_row($result))
{
}
$stop = microtime_float();
echo "<BR><BR>".($stop - $start)." [s].";
echo "<BR><BR><font color='green'>4. mysql_fetch_row() +
foreach()</b></font>";
while($info = mysql_fetch_row($result))
{
foreach($info as $field);
}
$stop = microtime_float();
echo "<BR><BR>".($stop - $start)." [s].";
echo "<BR><BR><font color='green'>5.
mysql_fetch_object()</b></font>";
while($object = mysql_fetch_row($result))
{
}
$stop = microtime_float();
echo "<BR><BR>".($stop - $start)." [s].";
------------------------------------------------------------------------
[2010-06-21 02:13:43] philip@php.net
Benchmarked with what code?
------------------------------------------------------------------------
[2010-06-19 18:13:22] skyeye at o2 dot pl
Description:
------------
"Note: Performance
An important thing to note is that using mysql_fetch_assoc() is not
significantly slower than using mysql_fetch_row(), while it provides a
significant added value. "
I compared mysql_fetch_assoc() and mysql_fetch_row(). These results show
that there is a huge difference between these functions.
Times for fetching 90000 records (there are 42 columns):
1. mysql_fetch_assoc() + array
2.20659708977 [s].
2. mysql_fetch_row + array
1.21593475342E-5 [s].
3. mysql_fetch_row + list()
2.40802764893E-5 [s].
4. mysql_fetch_row() + foreach()
4.19616699219E-5 [s].
5. mysql_fetch_object()
5.91278076172E-5 [s].
Times for fetching 50 records (there are 42 columns):
1. mysql_fetch_assoc() + array
0.00150012969971 [s].
2. mysql_fetch_row + array
1.09672546387E-5 [s].
3. mysql_fetch_row + list()
2.78949737549E-5 [s].
4. mysql_fetch_row() + foreach()
4.19616699219E-5 [s].
5. mysql_fetch_object()
5.50746917725E-5 [s].
Times for fetching 1 record (there are 42 columns):
1. mysql_fetch_assoc() + array
0.000169038772583 [s].
2. mysql_fetch_row + array
9.77516174316E-6 [s].
3. mysql_fetch_row + list()
3.50475311279E-5 [s].
4. mysql_fetch_row() + foreach()
4.91142272949E-5 [s].
5. mysql_fetch_object()
6.29425048828E-5 [s].
Times for fetching 90000 records (there is 1 column):
1. mysql_fetch_assoc() + array
0.10712313652 [s].
2. mysql_fetch_row + array
1.4066696167E-5 [s].
3. mysql_fetch_row + list()
4.88758087158E-5 [s].
4. mysql_fetch_row() + foreach()
6.98566436768E-5 [s].
5. mysql_fetch_object()
8.98838043213E-5 [s].
Times for fetching records (there is 1 column):
1. mysql_fetch_assoc() + array
0.000155925750732 [s].
2. mysql_fetch_row + array
1.09672546387E-5 [s].
3. mysql_fetch_row + list()
4.29153442383E-5 [s].
4. mysql_fetch_row() + foreach()
6.29425048828E-5 [s].
5. mysql_fetch_object()
8.01086425781E-5 [s].
Times for fetching 1 records (there is 1 column):
1. mysql_fetch_assoc() + array
0.000102996826172 [s].
2. mysql_fetch_row + array
1.31130218506E-5 [s].
3. mysql_fetch_row + list()
4.29153442383E-5 [s].
4. mysql_fetch_row() + foreach()
5.79357147217E-5 [s].
5. mysql_fetch_object()
7.20024108887E-5 [s].
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/bug.php?id=52127&edit=1