How can I count entries in mySQL database faster?

Question

I am counting the entries in my SQL database:

$sql = "SELECT * FROM files WHERE id = ?";
$q = $pdo->prepare($sql);
$q->execute([$id]);
$rowCount =$q->rowCount();

The result of $rowCount is 500000. But to output this single number takes 5 seconds! Is it possible to get this result faster?

I won't be able to answer it effectively. Have a look at some random stuff: https://stackoverflow.com/q/13051246 — Praveen Kumar Purushothaman, May 14 '18 at 10:33
@PraveenKumar Wouldn't `SELECT COUNT(id) FROM....` be quicker — RiggsFolly, May 14 '18 at 10:34
Although half a million row with the same `id` value makes me think that `id` should have a better more informative name :) — RiggsFolly, May 14 '18 at 10:37

d g · Accepted Answer · 2018-05-14T11:46:41.917

2

Use the COUNT() function https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html:

$sql = "SELECT COUNT(*) FROM files WHERE id = ?";

Also ensure that 'id' is an indexed column: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

edited May 14 '18 at 11:46

answered May 14 '18 at 11:40

d g

1,594
13
13

score 0 · Answer 2 · answered May 14 '18 at 11:26

0

Replace * with a field(use auto-increment id) - This will reduce the time a bit.
Index that field. - If you use indexed field the query performance will increase.

answered May 14 '18 at 11:26

Sankar Subburaj

4,992
12
48
79

score 0 · Answer 3 · answered May 21 '18 at 00:19

SELECT * ..., then counting in PHP, requires shoveling all columns of all rows back to PHP. That's a lot of effort for very little gain.

SELECT COUNT(col) ... does the counting in by MySQL, but it must check for whether col is NULL. And it needs to get at the value of col for every row.

SELECT COUNT(*) ... counts the rows by whatever way is most efficient. This involves looking for the 'smallest' index (or the whole table, if no secondary indexes), and counting through it.

You must learn about INDEXes to get anywhere in databases! This is only one minor use for them.

How can I count entries in mySQL database faster?

3 Answers3