MySQL Database Table Structure Efficiency Advice_问答_开发者

We are designing a MySQL table to track the number of followers on a daily basis for 10,000s of Twitter accounts. We've been struggling to figure out the most efficient way to store this data. The two options we are consider are:

1) OPTION 1 - Table with rows: Twitter ID, Month, Day1, Day2, Day3, etc. where each day would contain the number of followers for that account for each day of the specified month
2) OPTION 2 - Table with rows: Twitter ID, Day, Followers

Option 1 would result in about 30x less rows than Option 2. What I'm not sure from a performance perspective is if it's preferable to have less columns or less rows.

In terms of the queries we will be u开发者_如何学Gosing, we just want to be able to query the data to get the number of followers for a specific Twitter account for arbitrary time ranges.

I would appreciate suggestions on which approach is better and why. Also, if there is a much better option than the ones I present please feel free to suggest it.

Thanks in advance for your help!

Option 2, no question.

Imagine trying to write a query using each option. Let's give the best case for option 1: We know we want the total for all 31 days of the month. THen with option 1 the query is:

select twitterid, day1+day2+day3+day4+day5+day6+day7+day8+day9+day10
 +day11+day12+day13+day14+day15+day16+day17+day18+day19+day20
 +day21+day22+day23+day24+day15+day26+day27+day28+day29+day30
 +day31 as total
from table1
where month='2010-12';

select twitterid, sum(day) as total
from table2
where date between '2010-12-01' and '2010-12-31'
group by twitterid;

The second looks way easier to me. If you don't think so, tell me if you immediately noticed the error in the option 1 version, and if you're confident that no programmer would ever make such an error.

Now imagine that the requirements change just slightly, and someone wants the total for one week. With the second version, that's easy: give a date range that describes that week. This could easily be done when building a query on the fly: JUst ask for start date and add 6 days to it for the end date. But with the first version, what are you going to do? You'd have to figure out which days of the month fall in that week and change the list of fields retrieved. The week might span two calendar months. This would be a giant pain.

As to performance: Sure, more rows take more time to retrieve. But longer rows also take more time to retrieve. Lesson 1 on database design: Don't throw out normalization to do a micro-optimization when you don't even have a good reason to believe there's a problem. Build a normalized database first. Then if it turns out that there are performance problems, tune it afterwards. Odds are that you can buy a faster hard drive for a whole lot less than the cost of one day of programmer's time taken finding a mistake in an unnecessarily complex query.

Offcourse it depends on what queries you are going to do - but unless every query requires the 31 days of that month, for your operational data, Use Option 2.

It's better from a logical perspective (say later on you don't want queries per "30 calender days", but "last X days")
It's better for writes, too (only update 1 row with 2 fields instead of overwriting all fields).
You can always optimize later (partitioning comes to mind)
Your data-warehouse can still be optimized for long-term aggregate statistics.

Use Option 2. Option 1 would be a nightmare for queries. MySQL has good support for doing date ranges in queries, so it is easiest to just have row per day.

I would say option 2, but you would probably want to add a field for a primary key to speed up queries. And if that primary key is an integer value, even better.

Option 2 definitely (with a two-column unique key/constraint on Twitter ID and Day).

Option 1 will just be regrettable.