new data-dump in SQLite format available

Question

I've created an SQLite database containing the latest data-dump. It can be found at http://dl.getdropbox.com/u/1822417/so.7z I'd appreciate any input on features you'd like to see or changes in the existing schema. I'm not very experienced with databases so help with what to change or add would be appreciated. I'm planning on adding a tag table, as well as indexes, but not quite sure which fields would be best to index. When I'm happy with the database I'll find a more permanent place to host it and release the code used to create it.

Answer 1

Cool, thanks a lot.

I'd say let people create the indexes themselves. That saves you bandwidth.

People could then create indexes as they want—probably at least on all the keys (primary and foreign), i.e. anything that ends in Id. Retrieving all my posts (which is probably the first thing anyone is going to do [not mine but theirs]) took about 30 seconds without an index. With an index on the "OwnerId" column, I got the results instantly.

Indexes are cheap, because all they take is disk space. Of course it takes a while to create them, but after that, there's no need to watch for index update performance, because people are (probably) only going to use the data dump as read-only.

---

Here are the queries to create the indexes for everyone to copy and paste as they need:

Posts

Keys

CREATE INDEX idx_Posts_Id ON Posts (Id);
CREATE INDEX idx_Posts_AcceptedAnswerId ON Posts (AcceptedAnswerId);
CREATE INDEX idx_Posts_LastEditorId ON Posts (LastEditorId);
CREATE INDEX idx_Posts_OwnerId ON Posts (OwnerId);
CREATE INDEX idx_Posts_ParentID ON Posts (ParentID);
CREATE INDEX idx_Posts_PostTypeId ON Posts (PostTypeId);

Counts

CREATE INDEX idx_Posts_AnswerCount ON Posts (AnswerCount);
CREATE INDEX idx_Posts_CommentCount ON Posts (CommentCount);
CREATE INDEX idx_Posts_FavoriteCount ON Posts (FavoriteCount);
CREATE INDEX idx_Posts_Score ON Posts (Score);
CREATE INDEX idx_Posts_ViewCount ON Posts (ViewCount);

Dates

CREATE INDEX idx_Posts_ClosedDate ON Posts (ClosedDate);
CREATE INDEX idx_Posts_CommunityOwnedDate ON Posts (CommunityOwnedDate);
CREATE INDEX idx_Posts_CreationDate ON Posts (CreationDate);
CREATE INDEX idx_Posts_LastActivityDate ON Posts (LastActivityDate);
CREATE INDEX idx_Posts_LastEditDate ON Posts (LastEditDate);

Making this wiki; feel free to add for other tables.