Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently creating a web application using the twitter streaming API and would need some advice concerning my database scheme.

I basically have a list of hashtags that I track in real-time. All the tweets having that hashtag are stored in a table.

Thing is that the table is getting quickly huge, and it is far from efficient to crawl it afterwards (to get the list of tweets for a given user for example).

Just for information, I got something like 700.000 tweets crawled in less that 24 hours. This was for 5 hashtags, but I would like to scale up to a hundred at least.

I store them all and then populate another table of unique users out of it, which is transformed into a leaderboard.

So I was considering adding indexes, using the hashtag as a key. But I was also considering separating the tweets in several tables, one per hashtag in order to have a less juge amount of data to crawl.

The table would be created at runtime when a new hashtag is added.

Am I going the right way with this, or is that a bad practice?

Or is Mysql just a bas idea and I should go for another technology?

share|improve this question
1  
How "wide" is your hashtag column? Adding an index on varchar(255) is not recommended. –  Jack Jan 26 '13 at 0:37
    
as wide as a hashtag cna be. I'd say something like 10 characters should be way enough. –  jlengrand Jan 26 '13 at 0:39
    
Do you ever plan to track things like #omgthishastagissolong ? –  Michael Jan 26 '13 at 0:40
    
Sure it could even be 139 long if you take it that way. But It is highly probable that indexing only the first 10 caracters is enough, isn't it ? –  jlengrand Jan 26 '13 at 0:42
    
Yep, I was actually looking in it Michael. Is there a way to migrate questions ? –  jlengrand Jan 26 '13 at 0:44
show 4 more comments

migrated from stackoverflow.com Jan 26 '13 at 0:49

This question came from our site for professional and enthusiast programmers.

1 Answer

This is in general not the best practice. It depends upon the DB you are using, but generally a relational DB should be more efficient at handling the varying sets of rows if they are all in one table, than if you break the sets of rows into separate tables. If you let the DB manage the data, it will do a better job of optimizing IO than if you try to surround this with a bunch of tables in the file system.

Indeed, you should make the hashtag column indexed if you are going to commonly retrieve all the rows for a given hashtag.

There is a caveat: if you that you will only be processing one hashtag value at a time, and you will never want to have data from two hashtag values in process at the same time, there is a possibility that creating tables like you suggest might perform well. However, I doubt you are in this situation, and it is better to let the DB do what it is supposed to do.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.