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?