How hard is it to fix the Inbox notification ?

a reply to: ArMaP

How can the database engine know how many records exist with a specific, arbitrary condition without reading that field in all records, either from the table itself or from an index?

The DB engine will always have to perfrom some work. The difference is how much work. The engine (under conditions, usually with a proper schema and optimized indexes) doesn't neccesarily have to look at every record for a field.

Example: To get a count of number of records in a table, you dont need to look at every record, assuming you've properly added a pk to the table (you should) and that pk is AI (it mostly always should be) and your "deletes" are "soft" deletes, the engine can just simply return the value of max(pk) without ever looking at anything else in the table.

As far as how this works, the pk is an index, the engine (for mysql) stores indexes as a btree. You dont need to traverse the entire tree due to some implicit assumptions (the left leaf of the node is always lower than the right leaf, for example).

Given proper optimizations, the engine is very good at 'knowing' what can be ignored when it can be, of course its not magic.

a reply to: ASrubWhoDiedAgain

I understand it, thanks.
Although I have been working with databases for more than 25 years I learned it by using it, so I'm not familiar with how things really work.

But, once more, doesn't the amount or rows have a direct impact on the time it takes to get a conditional count, even if the field being counted is properly indexed?