StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
10844265
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2012-06-01T04:05:45.763
FavoriteCount
0
LastActivityDate
2012-06-01T04:12:51.850
LastEditDate
2012-06-01T04:12:51.850
LastEditorUserId
391130
OwnerUserId
391130
ParentId
10843334
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
This style of logging is practical, but you could benefit from using partitioning (and subpartitioning): <a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/partitioning.html</a> Since you're storing an activity log, you'll likely have a very large data set over time. Partitioning could be especially useful since you want to look at specific months and years. For example, if the data type for the date column is DATE or DATETIME, you could do something like: <pre><code>PARTITION BY RANGE (MONTH(the_date)) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN (1), PARTITION p2 VALUES LESS THAN (2), ...[and so on up to 12]); </code></pre> This will give you partitions for each month of data. Once you test with different partitions, try running your queries with "explain partitions select * from..." and you'll be able to see how the query is executed and which partitions are scanned. The best approach to partitioning likely needs to be tailored around your most common queries. For example, are you mainly looking at the last 30 days of data? Monthly snapshots? Custom date ranges? Those could all have an impact on how the partitions are structured. Taking it a step further, you could have subpartitions in each partition. For this, a hash partition could be used: <a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html</a> Your primary key question will be impacted by partitioning as well. The primary key (and any unique keys) in the table must use every column that are used in partitioning.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POIs this a practical concept for an activity log?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. UScszy
UserOwnerUserId
1. UScszy
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POIs this a practical concept for an activity log?
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. COPartitioning looks extremely promising, why have I never heard of this before? Thanks for the input!
 singulars
 PostPostId
 PO
 UserUserId
 USLitty

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.