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?