Fastest Way

[jaymc]

I want to count how many rows in a database match a members username

 

Whats the best way

 

At current I basically have

 

$q = "SELECT `id` FROM `table` WHERE `username` = 'fred'";

$num = mysql_num_rows($q);

 

Is that the fastest and less intensive way to count?

[chombone]

try 

 

   $querystring = "SELECT COUNT(*) FROM table WHERE username = 'fred'";
   $total_records = mysql_result(mysql_query($querystring,$connection),0);

[jaymc]

Is that know to be the best/fastest way to achieve a count of rows?

[jaymc]

What is $connection by the way?

[chombone]

$connection is the variable I use as a handle for the database connection

e.g.

       $connection = mysql_connect("localhost","username","password") or die ("Could not connect: " . mysql_error());
       mysql_select_db("databasename",$connection) or die ("Could not select db: " . mysql_error());

 

I don't know if COUNT is quicker, but the easy way to find out is create a large table and do some timings.

 

It's certainly more efficient in Oracle.

[jaymc]

What does the * represent? Is it always *

[leela]

Hello sir,

 

how to post questions thru my id????????

 

thanks

[Guest]

Instead of

 

$querystring = "SELECT COUNT(*) FROM table WHERE username = 'fred'";

 

You may prefer to use

 

$querystring = "SELECT COUNT(id) FROM table WHERE username = 'fred'";

 

Assuming id is a primary key. If you want to go farther, add an index on username. This would be optimal, I believe.

 

The difference isn't super profound right now (~0.0007ms on 300 records, ~0.0021ms on 3,000 records) but will be more appearant with a lot of records.

[fenway]

Definely the index on username!