> 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?
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 HTH, Set
