"Marcus Bointon" <marcus@xxxxxxxxxxxxxxxxxx> wrote in message news:D0420FA1-C5CC-42DB-ADCD-EBA24769B7AF@xxxxxxxxxxxxxxxxxxxxx > 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
