Count Alternative For Huge Databases

Question

EDIT: Count seems to not work no matter what I do. I was thinking of a different way of counting where I use some kind of SQL query that would re-compile all of the rows id's.

What I mean? Example Database:

ID - First Name
----------------
1 - John
3 - Joe
4 - Sal
9 - Murry

See it skips 2,5,6,7,8 because those rows were deleted. Is it possible to reset all current rows ids and auto increment them? If so then I could just do: SELECT id FROM table ORDER BY id DESC LIMIT 1 and that should take like a few seconds and if I dont delete any records it should be pretty precise too.

---

Count(*) is being so slow on my Database with about 800'000 rows.

Almost all the time I can't even load the page. Is there any faster alternative? Count(1) makes no difference.

This is my PHP:

This is the countrows function:

//Create a new function named query;
function countrows($sql = false,$dbname = false,$obj = false) {

    //Prepare The SQL Query;
    $query = Connect('localhost','shinymk_admin','password',$dbname)->prepare($sql);

    $res = true;

    //Execute Binded Query;
    try { $query->execute(); $count = $query->fetch(PDO::FETCH_NUM); }

    catch (PDOException $e) {

        $count = false;

    }

    //If no errors happened Make $row true;
    return $count[0];

}

Answer 1

The truth of the matter is that

  COUNT(*) AS rowcount FROM table

is the fastest way to get an accurate row count. If, on the other hand, you use

 COUNT(some_column) AS rowcount FROM table

it will count the number of rows with a non-NULL column value. That can, but doesn't have to be, stupid full-table-scan slow.

If you happen to be using the MyISAM access method, COUNT(*) should be very fast, because that access method keeps track of counts. If you're using InnoDB, it won't be quite as fast. But 800K records isn't really very many.

You may benefit from doing

 OPTIMIZE TABLE table

in this case where COUNT(*) is very slow.