|
|
Posted by "Satyam" on 06/15/33 11:23
"Marcus Bointon" <marcus@synchromedia.co.uk> wrote in message
news:D0420FA1-C5CC-42DB-ADCD-EBA24769B7AF@synchromedia.co.uk...
> According to the docs, MySQL has a particular optimisation that means
> that it should be:
>
> $sql = "SELECT COUNT(*) FROM tbvara WHERE Varunamn LIKE '$checkLev%'";
>
It is not just a MySql optimization but this holds true for every database.
When you specify a field in the count() function, you are telling SQL to
count the number of occurences of that field which does not include those
records in which the field is set tu null. Thus, to do such count, SQL has
to access each record to check whether the field is null or not. For a
table where a certain field allows nulls, asking for a count of that field
will give a lower row count than asking for count(*).
On the other hand, if you say count(*) SQL knows that you mean a plain
recordcount, regardless of whether any particular field is null or not. To
do that, SQL does not need to access the actual data in each record, it just
needs to travel the primary key tree, never getting to the actual records.
Some database engine might be smart enough to figure that if the particular
field you asked for does not allow nulls, a count of that field is the same
as a count(*) and us a better strategy but you are better off not relying on
that.
By the way, I wouldn't count on mysql_num_rows() being as fast as doing a
count(*). The SQL engine can play a lot of tricks knowing for certain that
you just mean a count(*) which cannot do if you do a plain query which you
don't actually mean to use but for counting. I would assume that depending
on the engine, a whole batch of records would be read into memory to have
them ready for the fetches that never come.
It is safer to explicitly tell the SQL engine what you actually mean than to
rely on smart optimizations that might or might not be there.
Satyam
Navigation:
[Reply to this message]
|