Danilo Domínguez P. Site

Haciendo una paginación para un proyecto me dió la curiosidad de verificar cuál método es más efectivo para contar el total de registros. Bueno a pesar de ser obvio que con sql count(*) sería más rápido, me propuse hacer las pruebas y estos fueron los resultados:

• Prueba con SELECT COUNT(*) FROM tabla Fecha: 22/04/2008 11:35:17 am Test con count(*) - registros = 44069 Tiempo 0.00441598892212 segundos
• Prueba mysql_num_rows Fecha: 22/04/2008 11:35:21 am Test con mysql_affected_rows - registros = 44069 Tiempo 59.3658621311 segundos

Código fuente de la prueba:

<span style="color: #000000;">1.
<span style="color: #ff0000;"><span style="color: #0000bb;">2.    </span><span style="color: #007700;">require(</span><span style="color: #dd0000;">'../benchmark.php'</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">3.    $caso </span><span style="color: #007700;">= </span><span style="color: #dd0000;">'mysql'</span><span style="color: #007700;">;</span><span style="color: #0000bb;">
4.    $file </span><span style="color: #007700;">= </span><span style="color: #dd0000;">'../var/bench_databasecount.txt'</span><span style="color: #007700;">;</span><span style="color: #0000bb;">
5.    </span><span style="color: #007700;">try{
</span><span style="color: #0000bb;">6.        Benchmark</span><span style="color: #007700;">::</span><span style="color: #0000bb;">startBenchmark</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$file</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">7.        $conn </span><span style="color: #007700;">= </span><span style="color: #0000bb;">mysql_connect</span><span style="color: #007700;">(</span><span style="color: #dd0000;">'localhost'</span><span style="color: #007700;">, </span><span style="color: #dd0000;">'root'</span><span style="color: #007700;">, </span><span style="color: #dd0000;">'pannet1'</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">8.        mysql_select_db</span><span style="color: #007700;">(</span><span style="color: #dd0000;">'test'</span><span style="color: #007700;">, </span><span style="color: #0000bb;">$conn</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">9.        </span><span style="color: #007700;">switch(</span><span style="color: #0000bb;">$caso</span><span style="color: #007700;">){</span><span style="color: #0000bb;">
10.            </span><span style="color: #007700;">case </span><span style="color: #dd0000;">'count'</span><span style="color: #007700;">:
</span><span style="color: #0000bb;">11.                    $sql </span><span style="color: #007700;">= </span><span style="color: #dd0000;">"SELECT COUNT(*) as contador FROM tabla"</span><span style="color: #007700;">;
</span><span style="color: #0000bb;">12.                    $rs </span><span style="color: #007700;">= </span><span style="color: #0000bb;">mysql_query</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$sql</span><span style="color: #007700;">, </span><span style="color: #0000bb;">$conn</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">13.                    $count </span><span style="color: #007700;">= </span><span style="color: #0000bb;">mysql_fetch_array</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$rs</span><span style="color: #007700;">);</span><span style="color: #0000bb;">
14.                    Benchmark</span><span style="color: #007700;">::</span><span style="color: #0000bb;">pushLog</span><span style="color: #007700;">(</span><span style="color: #dd0000;">'Test con count - registros = '</span><span style="color: #007700;">.</span><span style="color: #0000bb;">$count</span><span style="color: #007700;">[</span><span style="color: #dd0000;">'contador'</span><span style="color: #007700;">].</span><span style="color: #dd0000;">"\n"</span><span style="color: #007700;">, </span><span style="color: #0000bb;">$file</span><span style="color: #007700;">);</span><span style="color: #0000bb;">
15.            </span><span style="color: #007700;">break;</span><span style="color: #0000bb;">
16.            </span><span style="color: #007700;">case </span><span style="color: #dd0000;">'mysql'</span><span style="color: #007700;">:
</span><span style="color: #0000bb;">17.                    $sql </span><span style="color: #007700;">= </span><span style="color: #dd0000;">"SELECT * FROM tabla"</span><span style="color: #007700;">;</span><span style="color: #0000bb;">
18.                    $rs </span><span style="color: #007700;">= </span><span style="color: #0000bb;">mysql_query</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$sql</span><span style="color: #007700;">, </span><span style="color: #0000bb;">$conn</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">19.                    $count </span><span style="color: #007700;">= </span><span style="color: #0000bb;">mysql_num_rows</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$rs</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">20.                    Benchmark</span><span style="color: #007700;">::</span><span style="color: #0000bb;">pushLog</span><span style="color: #007700;">(</span><span style="color: #dd0000;">'Test con mysql_affected_rows - registros = '</span><span style="color: #007700;">.</span><span style="color: #0000bb;">$count </span><span style="color: #007700;">.</span><span style="color: #dd0000;">"\n"</span><span style="color: #007700;">, </span><span style="color: #0000bb;">$file</span><span style="color: #007700;">);
</span><span style="color: #0000bb;">21.            </span><span style="color: #007700;">break;
</span><span style="color: #0000bb;">22.
23.        </span><span style="color: #007700;">}
</span><span style="color: #0000bb;">24.        Benchmark</span><span style="color: #007700;">::</span><span style="color: #0000bb;">endBenchmark</span><span style="color: #007700;">(</span><span style="color: #0000bb;">$file</span><span style="color: #007700;">);</span><span style="color: #0000bb;">
25.        </span><span style="color: #007700;">echo </span><span style="color: #dd0000;">'Termino'</span><span style="color: #007700;">;</span><span style="color: #0000bb;">
26.    </span><span style="color: #007700;">}catch(</span><span style="color: #0000bb;">Exception $e</span><span style="color: #007700;">){
</span><span style="color: #0000bb;">27.        </span><span style="color: #007700;">echo </span><span style="color: #0000bb;">$e</span><span style="color: #007700;">-&gt;</span><span style="color: #0000bb;">getMessage</span><span style="color: #007700;">();</span><span style="color: #0000bb;">
28.    </span><span style="color: #007700;">}</span><span style="color: #0000bb;">
29.    ?&gt;</span></span></span>

Conclusión A las personas que todavía realizan las paginaciones con mysql_num_rows, preferiblemente usen sql (función COUNT(*)). Saludos ...

Promedio: Elija un puntajePoorOkayGoodGreatAwesome

Su voto: Nada Promedio: 5 (1 vote)