SQL aggregate function performance in a table with many records that is well-indexed
Is it common to spread data of a single user operation across multiple rows?


Think of the "entities":  User, Review, Rating.  A "user" may go to the restaurant many times, and give a "review" each time.  The "review" may give ratings for multiple things, such as 'food' and 'location'.

These are "Many-to-one" relationships, so Ratings has a review_id and Reviews has a user_id.  ("Many-to-many" relationships need an extra table.)

Yes, database optimizer can usually use indexes as needed.  We need to look at the specific SELECTs to say further.

KEY `nid` (`nid`), -- Redundant with the next; DROP this.
KEY `nid_uid` (`nid`,`uid`),


It looks like you did a shotgun approach to adding INDEXes.  Do EXPLAIN SELECT ... on your various SELECTs -- This is to see which indexes are actually used.  Then DROP the unused queries.

Every table should (must?) have a PRIMARY KEY.  For Ratings, it should probably be PRIMARY KEY(review_id, type).

Your "reports" are likely to do JOINs and may end up COUNTing and SUMming things extra times, so beware.  Do a sanity check on the results.  The overages can be fixed, but I'll let you get to that point before trying to explain the problem.