Re: [PHP] Fast count of recordset in php...
"Marcus Bointon" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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
Re: [PHP] Fast count of recordset in php...
On 7 Aug 2005, at 05:04, TalkativeDoggy wrote: be coz this way is slow and costs more overload. $sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE '$checkLev%'"; $querys = mysql_query($sql); //Count products in db // $dbArray = mysql_fetch_row($querys); $nrOfProducts = $dbArray[0]; According to the docs, MySQL has a particular optimisation that means that it should be: $sql = "SELECT COUNT(*) FROM tbvara WHERE Varunamn LIKE '$checkLev%'"; Also if you want the full count value when you've done a query that uses a LIMIT clause, instead of doing a separate full count query, you can get it by using the SQL_CALC_FOUND_ROWS keyword, e.g. $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM tbvara WHERE Varunamn LIKE '$checkLev%' LIMIT 10"; Of course count(*) could only ever return up to 10 in this query - you can find out how many it would have found by then asking: $sql = "SELECT FOUND_ROWS()"; Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Fast count of recordset in php...
Hi again!
Thanx!!! I think this will help a lot!!!
/mr G
@varupiraten.se
- Original Message -
From: "TalkativeDoggy" <[EMAIL PROTECTED]>
To: "Sebastian" <[EMAIL PROTECTED]>
Cc: "Gustav Wiberg" <[EMAIL PROTECTED]>;
Sent: Sunday, August 07, 2005 6:04 AM
Subject: Re: [PHP] Fast count of recordset in php...
if you just wana get count of recordset,
don't do this:
$sql = mysql_query("SELECT col FROM tbvara WHERE Varunamn LIKE
'$checkLev%'");
// record count
echo mysql_num_rows($sql);
be coz this way is slow and costs more overload.
$sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
'$checkLev%'";
$querys = mysql_query($sql);
//Count products in db
//
$dbArray = mysql_fetch_row($querys);
$nrOfProducts = $dbArray[0];
Sebastian wrote:
you'd be suprized how fast an index can be.. you should read the manual
on indexes,
http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
"If a table has 1,000 rows, this is at least 100 times faster than
reading sequentially"
while you may not get 100x faster, it will be faster than having no
index.. also try using mysql_num_rows()
$sql = mysql_query("SELECT col FROM tbvara WHERE Varunamn LIKE
'$checkLev%'");
// record count
echo mysql_num_rows($sql);
PS, 'reply all' to list.
Gustav Wiberg wrote:
Hi there!
There are about 300 records.
You're right, I can add an index, but Is that the only way to get a
faster solution?
/mr G
@varupiraten.se
- Original Message ----- From: "Sebastian"
<[EMAIL PROTECTED]>
To: "Gustav Wiberg" <[EMAIL PROTECTED]>
Sent: Saturday, August 06, 2005 11:08 PM
Subject: Re: [PHP] Fast count of recordset in php...
how many records in the table? its going to be slow no matter what,
especially if you have a lot of records.. your searching each record
then counting them.. do you have any indexes? judging by your query
you can add index on Varunamn and it should be more speedy..
Gustav Wiberg wrote:
Hello there!
How do i get a fast count of a recordset in php?
Look at this code:
$sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
'$checkLev%'";
$querys = mysql_query($sql);
//Count products in db
//
$dbArray = mysql_fetch_array($querys);
$nrOfProducts = $dbArray["cn"];
It's slow... Why? Any suggestions?
/mr G
@varupiraten.se
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 2005-08-03
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Fast count of recordset in php...
if you just wana get count of recordset,
don't do this:
$sql = mysql_query("SELECT col FROM tbvara WHERE Varunamn LIKE
'$checkLev%'");
// record count
echo mysql_num_rows($sql);
be coz this way is slow and costs more overload.
$sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
'$checkLev%'";
$querys = mysql_query($sql);
//Count products in db
//
$dbArray = mysql_fetch_row($querys);
$nrOfProducts = $dbArray[0];
Sebastian wrote:
you'd be suprized how fast an index can be.. you should read the manual
on indexes,
http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
"If a table has 1,000 rows, this is at least 100 times faster than
reading sequentially"
while you may not get 100x faster, it will be faster than having no
index.. also try using mysql_num_rows()
$sql = mysql_query("SELECT col FROM tbvara WHERE Varunamn LIKE
'$checkLev%'");
// record count
echo mysql_num_rows($sql);
PS, 'reply all' to list.
Gustav Wiberg wrote:
Hi there!
There are about 300 records.
You're right, I can add an index, but Is that the only way to get a
faster solution?
/mr G
@varupiraten.se
- Original Message - From: "Sebastian"
<[EMAIL PROTECTED]>
To: "Gustav Wiberg" <[EMAIL PROTECTED]>
Sent: Saturday, August 06, 2005 11:08 PM
Subject: Re: [PHP] Fast count of recordset in php...
how many records in the table? its going to be slow no matter what,
especially if you have a lot of records.. your searching each record
then counting them.. do you have any indexes? judging by your query
you can add index on Varunamn and it should be more speedy..
Gustav Wiberg wrote:
Hello there!
How do i get a fast count of a recordset in php?
Look at this code:
$sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
'$checkLev%'";
$querys = mysql_query($sql);
//Count products in db
//
$dbArray = mysql_fetch_array($querys);
$nrOfProducts = $dbArray["cn"];
It's slow... Why? Any suggestions?
/mr G
@varupiraten.se
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Fast count of recordset in php...
you'd be suprized how fast an index can be.. you should read the manual
on indexes,
http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
"If a table has 1,000 rows, this is at least 100 times faster than
reading sequentially"
while you may not get 100x faster, it will be faster than having no
index.. also try using mysql_num_rows()
$sql = mysql_query("SELECT col FROM tbvara WHERE Varunamn LIKE
'$checkLev%'");
// record count
echo mysql_num_rows($sql);
PS, 'reply all' to list.
Gustav Wiberg wrote:
Hi there!
There are about 300 records.
You're right, I can add an index, but Is that the only way to get a
faster solution?
/mr G
@varupiraten.se
- Original Message - From: "Sebastian"
<[EMAIL PROTECTED]>
To: "Gustav Wiberg" <[EMAIL PROTECTED]>
Sent: Saturday, August 06, 2005 11:08 PM
Subject: Re: [PHP] Fast count of recordset in php...
how many records in the table? its going to be slow no matter what,
especially if you have a lot of records.. your searching each record
then counting them.. do you have any indexes? judging by your query
you can add index on Varunamn and it should be more speedy..
Gustav Wiberg wrote:
Hello there!
How do i get a fast count of a recordset in php?
Look at this code:
$sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE
'$checkLev%'";
$querys = mysql_query($sql);
//Count products in db
//
$dbArray = mysql_fetch_array($querys);
$nrOfProducts = $dbArray["cn"];
It's slow... Why? Any suggestions?
/mr G
@varupiraten.se
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.1/64 - Release Date: 8/4/2005
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date:
2005-08-03
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.1/64 - Release Date: 8/4/2005
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Fast count of recordset in php...
$num=mysql_num_rows($sql); Gustav Wiberg wrote: Hello there! How do i get a fast count of a recordset in php? Look at this code: $sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE '$checkLev%'"; $querys = mysql_query($sql); //Count products in db // $dbArray = mysql_fetch_array($querys); $nrOfProducts = $dbArray["cn"]; It's slow... Why? Any suggestions? /mr G @varupiraten.se -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Fast count of recordset in php...
Hello there! How do i get a fast count of a recordset in php? Look at this code: $sql = "SELECT COUNT(IDVara) cn FROM tbvara WHERE Varunamn LIKE '$checkLev%'"; $querys = mysql_query($sql); //Count products in db // $dbArray = mysql_fetch_array($querys); $nrOfProducts = $dbArray["cn"]; It's slow... Why? Any suggestions? /mr G @varupiraten.se -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
