0

I want to count the exact no. of rows in my table, this is how I'm doing. Let me know if I'm doing any blunder in terms of performance.

Old way

//I'm doing this to verify if the entered User name is correct.

$stmt = $conn->prepare("SELECT * FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$Count = $stmt->rowCount();
echo $Count;

In some site, I read that the use of SELECT * has some performance issues so it should be avoided if all the columns are not required so I have changed to

$stmt = $conn->prepare("SELECT `id` FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$Count = $stmt->rowCount();
echo $Count;

I thought of using MYSQL function COUNT() though I'm not sure if it will boost the performance in tables with few million rows and approximately 8-9 columns.

$stmt = $conn->prepare("SELECT COUNT(`id`) FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
for($stmt as $row)
    $Count = $row[0];
echo $Count;

Which one out of the above should I use or is there are some other better methods?

NewBee
  • 394
  • 3
  • 15
  • Possible duplicate of [MySQL: Fastest way to count number of rows](https://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows) – Nigel Ren Jun 07 '18 at 09:13
  • @NigelRen - yeah a duplicate, but that Q&A is so riddled with misstatements that it is hard to ferret out the truth. – Rick James Jun 08 '18 at 02:07

1 Answers1

3

Using COUNT(*) is more efficient than fetching all rows and then count.

So you should use:

$stmt = $conn->prepare("SELECT COUNT(`id`) FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$count = $stmt->fetchColumn();
echo $Count;

Read more about fetchColumn

Lovepreet Singh
  • 4,792
  • 1
  • 18
  • 36
  • It would be useful to add some form of explanation of why they should use this method. – Nigel Ren Jun 07 '18 at 09:15
  • Thanks, it worked. How about if I use only`$count = $stmt->fetch()`? Ref: http://php.net/manual/en/pdostatement.rowcount.php#107055 – NewBee Jun 07 '18 at 13:02
  • 1
    With `$count = $stmt->fetch()`, need to get count as `$count = $count[0]`; – Lovepreet Singh Jun 07 '18 at 13:05
  • Yes done that. In this case, `fetchColumn()` is working perfectly but found a limitation in `fetchColumn()` http://php.net/manual/en/pdostatement.fetchcolumn.php#refsect1-pdostatement.fetchcolumn-returnvalues. As you have mentioned not to fetch all rows so I'm replacing some `foreach` loops with `fetch()`, where multiple column values of single row is required. Let me know if I'm doing it wrong by replacing the `foreach` loops. – NewBee Jun 07 '18 at 13:30
  • 1
    If you need multiple/all records, then use `$stmt->fetchAll()`, will return array of records & use `foreach`. For single row, use `$stmt->fetch()`, will return single record. And for single column use `$stmt->fetchColumn()`, will return first column only like `count`. – Lovepreet Singh Jun 07 '18 at 16:28
  • But... If you need to fetch all the rows anyway, then asking PHP `count($rows)` is faster. – Rick James Jun 08 '18 at 02:08