How to count recors in more efficient way

firebirdsql wrote:

CREATE TABLE posts
(
  board_id,
  username,
  PRIMARY KEY (board_id, username)
)

SELECT COUNT(*) FROM posts WHERE board_id = 1;

There are 123k rows in the posts table (just a sample) and all of them are of board_id = 1. This query takes about 0.5-1 sec to run (slow).

I'm guessing the performance is due to poor index selectivity. However, there's always going to be only a couple of boards so the index is always going to be poor selectivity.

In this case, is it better to store the count as a column or is there any optimization that can be done?

Svein Erling Tysvær answers:

I'd guess (I don't know) the 'slowness' is due to Firebird needing to check for each record whether it is visible to the current transaction - there may be records that shouldn't be counted since they're not visible and reversely records that should be counted even though they are deleted in another transaction.

If the plan include the PK index, you might get slightly better selectivity by changing to WHERE board_id+0 = 1. However, it might be that it already use NATURAL, and then this modification will make no difference.

If counting the records is something that you're likely to do fairly often (it might not be worth it, if it is just for display purposes), then you could add another table:

CREATE TABLE post_count
(
  PK_POST_COUNT integer not null
  board_id      integer not null,
  MyCount       integer not null,
  PRIMARY KEY (PK_POST_COUNT)
);

and add a few triggers:

1. I generally recommend having meaningless fields as primary keys, not usernames or other meaningful fields that theoretically can change definition in the future, so I'd have a trigger that used a generator to fill PK_POST_COUNT.

2. An ON AFTER INSERT trigger that contains

   INSERT INTO post_count(board_id, MyCount) VALUES (new.board_id, 1);
   

3. An ON AFTER UPDATE trigger that contains

   INSERT INTO post_count(board_id, MyCount) VALUES (old.board_id, -1);
   INSERT INTO post_count(board_id, MyCount) VALUES (new.board_id, 1);
   

4. An ON AFTER DELETE trigger that contains

   INSERT INTO post_count(board_id, MyCount) VALUES (old.board_id, -1);
   

Then, I'd regularly (e.g. once each night) run a procedure containing something like:

FOR SELECT board_id, SUM(MyCount) FROM post_count group by 1 into :TmpBoard_id, :TmpMyCount
DO
BEGIN
  DELETE FROM post_count WHERE board_id = :TmpBoard_id;
  INSERT INTO post_count(board_id, MyCount) VALUES (:TmpBoard_id, :TmpMyCount);
END

Then, finding the count would be a simple SQL statement:

SELECT SUM(MyCount)
FROM post_count
WHERE board_id = :MyBoardID