CODEWITHSUNDEEP
mysqldatabasedatabase-design
seferov
seferov

Reputation: 4161

storing the count of rows or just count the rows?

To exemplify my situation, think of a Question & Answer platform like stackoverflow. Basically there are questions and answers tables. What is the best practice for displaying the number of answers of a specific question?

Upvotes: 1

Views: 518

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53840

I would keep it simple for now, and use COUNT to return the rows, but there are some caveats that you should be aware of.

MySQL is able to use an index for COUNT(*) queries, so MySQL won't have to hit the actual table row data to get a count. The caveat is that if you attempt a COUNT(column) query against a column that allows NULL values, MySQL will still have to scan the rows to count the NULL values, since NULL values aren't stored in an index.

Therefore, be certain that you ask for COUNT against columns that are defined as NOT NULL.

MyISAM tables store row counts, so a COUNT(*) query for all rows in a table is super lightweight. This is possible since MyISAM locks the whole table, forcing sequential updates. Hence, MyISAM, always knows how many rows.

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67075

I would not worry about load on the database unless you actually find it to be a problem. In fact, the option of keeping a count requires an additional update on every CRUD operation, so I would think that would be more overhead than an occasional count. Go with KISS first, and if you find a problem, then fix it :)

Upvotes: 4

Related Questions