firebirdsql wrote:

  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,

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
  DELETE FROM post_count WHERE board_id = :TmpBoard_id;
  INSERT INTO post_count(board_id, MyCount) VALUES (:TmpBoard_id, :TmpMyCount);

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

FROM post_count
WHERE board_id = :MyBoardID

Like this post? Share on: TwitterFacebookEmail

Related Articles


Firebird Community



Gems from Firebird Support list