Is it more efficient to store counts from SQL? What are the downsides (synchronization etc.)?

Question

I have a MySQL database with two tables I am interested in querying:

Users: Stores information about users such as userID etc.

Map: A map table containing about 7 million mapIDs (an index referring to a physical lat/long on earth).

Many of these mapIDs are associated to userIDs, so for example user #1 may have 10 mapIDs associated with him, user #2 may have 100 etc.

I am interested in knowing what is more efficient/safer/best practice to count how many mapIDs belong to a user when I query the database with a userID:

1) Query the Map table to count how many mapIDs belong to the userID, OR

2) Store the number of mapIDs belonging to users in an additional column in the Users table (e.g. mapCount), and only query this value (rather than searching the large Maps table each time).

I know option 2 will be faster, but I am worried about potential problems with synchronization etc. For example, every time a user performs an action (e.g. add a mapID to his account) I would add the userID to the associated mapID on the Maps table, and also increment the mapCount value in Users so that subsequent searches/actions will be faster. But what if the second query failed for some reason and the mapCount field fell out of synch? Is this worth the risk?

What is generally the best thing to do in this situation?

Answer 1

Option 1 reduces the need for an additional write, is easier to implement and maintain, and the read performance difference will be so marginal there's no point in measuring it yet.

Answer 2

If you are building the database, start by using a query to extract the data you want using a query. You can optimize this query by adding an index on map(usersId). If the performance is adequate, you are done.

If performance is not sufficient, then you can consider storing the count separately. Maintaining the count requires triggers on insert and delete and possibly on update.

These triggers will have an effect on performance when adding and modifying data. This is usually small, but it can be important. If you are doing bulk-load operations, then you will need to manually handle the summarization values.

All this maintenance is a lot of work, and you should only go down that path if you really need to do it that way.

Answer 3

You are facing one of the classic database design trade offs: speed vs. accuracy / synchronization. If your DBMS supports triggers, you could denormalize the count into the user table via a trigger on the maps table, in which case you would no longer have to worry about accuracy. This is about as detailed as my answer can be until we know more about your DBMS.