Mysql – Economic Database Architecture Possibilities w/ Data of Varying Length

Things to try:

Adding an index on (user_id, date, score)

Group by only on scores table and then join to users:

SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
  JOIN 
  ( SELECT user_id, SUM(score) AS total
    FROM scores
    WHERE date >= '2012-01-01' AND date < '2012-02-01'
    GROUP BY user_id
    HAVING SUM(score) >= 1000
    ORDER BY total DESC LIMIT 50
  ) AS s
      ON u.id = s.user_id
ORDER BY total DESC ;

Countries are relatively stable so it is easy to have a countries table. City names can be duplicated multiple times within a country. Maintaining the city table may be difficult for a couple of reasons.

• Some cities have different spellings in different languages.
• Some countries have multiple cities with the same name. These are likely in different states or provinces.

You may want to allow free format for the city and keep it in the user table. Otherwise, investigate adding a state/province table. You will still need to deal with how to add new cities as needed.

If you index the sender, the count can be done on the index. This can be extremely efficient for smaller counts. Updating the user table for each message will increase the log data volume, and increase the cost of saving each message. It is also error prone, and can lead to deadlocks if you are not consistent in the order the accesses are done. De-normalizing the model should be done only after determining you have a performance problem.

A query like SELECT COUNT(1) FROM message WHERE sender=?ID is likely better than maintaining a count.