1

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 using, 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!

Improve this question

5 Answers 5

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+1. I agree with this in so many ways I would have created an exact replica had this answer not been here.
+1. Nothing explains things better than clear and simple examples! Well formed and easy read answer!
1

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.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

Even if that means that we might have to scale up to 100 million rows as we scale the service? Is there any major performance issue you're aware of as the number of rows grows?If we restricted the date range for queries to specific months so we would be selecting entire rows in Option 1 would that change your answer?
Database systems are built to handle lots of rows. Any major issues you have with 100M rows you will probably also have with 30M rows. If anything you might want to partition the tables by month so they are physically stored grouped by month, but that is more a function of tuning your DB storage layer, not your schema.
0

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.

Improve this answer

Comments

0

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

Option 1 will just be regrettable.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.