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'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal?

Here is the schema in Rails format (SQL format below as well):

  create_table "comments", force: true do |t|
    t.integer  "id"
    t.integer  "post_id",                null: false
    t.integer  "user_id",                null: false
    t.text     "body",                   null: false
    t.integer  "score",      default: 0, null: false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "post_types", force: true do |t|
    t.integer  "id"
    t.string "name", null: false
  end

  create_table "posts", force: true do |t|
    t.integer  "id"
    t.integer  "post_type_id",       limit: 2,               null: false
    t.integer  "accepted_answer_id"
    t.integer  "parent_id"
    t.integer  "user_id",                                    null: false
    t.text     "title",              limit: 255,             null: false
    t.text     "body",                                       null: false
    t.integer  "score",                          default: 0, null: false
    t.integer  "views",                          default: 1, null: false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "posts_tags", force: true do |t|
    t.integer  "id"
    t.integer "post_id", null: false
    t.integer "tag_id",  null: false
  end

  create_table "tag_synonyms", force: true do |t|
    t.integer  "id"
    t.string "source_tag", null: false
    t.string "synonym",    null: false
  end

  create_table "tags", force: true do |t|
    t.integer  "id"
    t.string "name", null: false
  end

  create_table "users", force: true do |t|
    t.integer  "id"
    t.string   "first_name",   limit: 50
    t.string   "last_name",    limit: 50
    t.string   "display_name", limit: 100,             null: false
    t.string   "email",        limit: 100,             null: false
    t.string   "password",                             null: false
    t.string   "salt",                                 null: false
    t.string   "about_me"
    t.string   "website_url"
    t.string   "location",     limit: 100
    t.integer  "karma",                    default: 0, null: false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "vote_types", force: true do |t|
    t.integer  "id"
    t.string "name", null: false
  end

  create_table "votes", force: true do |t|
    t.integer  "id"
    t.integer  "post_id",      null: false
    t.integer  "vote_type_id", null: false
    t.integer  "user_id",      null: false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

Here is the raw structure in SQL as well:

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `post_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_type_id` smallint(6) NOT NULL,
  `accepted_answer_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `title` tinytext NOT NULL,
  `body` text NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `posts_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tag_synonyms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_tag` varchar(255) NOT NULL,
  `synonym` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `display_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `salt` varchar(255) NOT NULL,
  `about_me` varchar(255) DEFAULT NULL,
  `website_url` varchar(255) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `karma` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `vote_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `vote_type_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);
share|improve this question

migrated from codereview.stackexchange.com Dec 19 '13 at 23:05

This question came from our site for peer programmer code reviews.

1  
Based on note about rails adding an id column: can you please show us what your actual table looks like then? Does it have indexes at all? This question should be closed as 'insufficient information'... in fact. –  rolfl Dec 19 '13 at 14:51
1  
After your added comment, it makes it clear that each table has an 'id', but, not whether that ID is in fact a PRIMARY KEY (using a database definition of PRIMARY KEY, not a Ruby definition). You are asking for database assistance, show us what the database sees, not what Ruby sees. Currently, as it is, your code 'does not work' and has 'syntax errors'. –  rolfl Dec 19 '13 at 14:57
    
Adding in the IDs in the question for clarity. Primary keys automatically get an index by the DB. Those are the only indexes right now. –  Hopstream Dec 19 '13 at 14:57
    
@rolfl Added both format (Rails and SQL). Had posted just the Rails since it's cleaner to read quickly. –  Hopstream Dec 19 '13 at 15:03
1  
Rails is only cleaner and easy to read if you are a rails person. Don't ask a mysql performance question without giving the mysql source.... –  rolfl Dec 19 '13 at 15:04

2 Answers 2

up vote 6 down vote accepted

Let's go though a few things here... (now that you actually show the database structure instead of only the Rails 'view', we can see what's happening).....

"Relational Databases" are about "Relationships". Relationships are expressed by having queries 'join' two or more tables. The Joins require matching columns on both tables. For example, the post_id on the comment table matches the id on the posts table.

If you have some comments and want to find the details of the posts they are on, then you will want to select from the posts table where the comment_id is a certain (set of) values.

When you select on a column, you often (normally) want that column to be indexed.

So, for each of your 'primary key' columns you will automatically also have an index. You need to index the 'other' side of the relationship as well.

Comments table

created_at should not be nullable. Nullable columns typically have a small impact on performance. All comments are created, and thus should all have a date, and there is no need for it to be null.

If you do queries that select the comments on a particular post, then you need an index on the post_id.

I suspect you may also have occasional queries for all the posts for a given user, which means you will probably want another index on the user_id

post_types

No problems here.

Posts

You will want indexes on the following:

  • if you want to select the post for a given parent, parent_id
  • if you want to select posts for a given user, then user_id
  • if you want to select posts for a given type, then post_type_id
  • you will also want to index the title, since this may make searches easier.
  • look in to full-text indexing for the body.

Should created_date be nullable?

Post-Tags

you will want two indexes here, and for performance reasons, you will probably want them duplicated. Explaining why is beyond this answer, but look for 'index coverage':

  • index on both tag_id and post_id
  • index on both post_id and tag_id

Tag Synonyms

source_tag should be source_id and should be an integer. Also with an index. synonym should be synonym_id and should be an integer. It should also have an index.

Tags

Fine

Users

Recommend an index on:

  • display_name - so people can find themselves easily (and hopefully you have enough users for it to be needed).

(Are you sure you don't mind the users having no name)

Should created_date be nullable?

Vote_Types

fine

Votes

vote_type_id, post_id and user_id should each have their own index.

Should created_date be nullable?

Conclusion

Now you have some suggestions on what indexes you should start with, the next step is monitoring where your actual performance is poor, and targeting those areas for additional optimization. To do that, you need to actually be running your application, and finding out what your actual queries look like, and running those queries to see what the actual execution plans are, and where those plans look like they need help by adding an index.

- you do not have any primary keys on your database. Primary keys are part of the database's referential integrity, and ensure that you and your programs do the 'right thing'. Additionally, primary keys are implemented as an index, so they will ensure that primary-key-related access to your table is fast. - you do not have a post_id column on your post table????? Really? This makes no sense.... unless parent_id is supposed to be the unique identifier..... - similarly, you do not have a user_id on the users table. What gives?

So, you have no keys, and as a result, you are missing what are normally the most critical indices. Set up each table to have a key and you will be most of the way there.

Most databases now contain tools that will recommend indexes for you based on queries that you often run.

share|improve this answer
    
Hi, sorry if I didn't make it clear in my question -- rails auto adds the primary keys on each table that is why this schema doesn't show it explicitly –  Hopstream Dec 19 '13 at 14:49
    
@rolfl, you totally shadowed me –  Malachi Dec 19 '13 at 16:31

With @rolfl's helpful answer, here is the schema I ended up going with in case anything finds it useful.

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `body` text NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_comments_on_post_id` (`post_id`),
  KEY `index_comments_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `post_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_type_id` smallint(6) NOT NULL,
  `accepted_answer_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `title` tinytext NOT NULL,
  `body` text NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_posts_on_post_type_id` (`post_type_id`),
  KEY `index_posts_on_parent_id` (`parent_id`),
  KEY `index_posts_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `posts_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`),
  KEY `index_posts_tags_on_tag_id_and_post_id` (`tag_id`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tag_synonyms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `synonym` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_tag_synonyms_on_synonym` (`synonym`),
  KEY `index_tag_synonyms_on_tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `display_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `salt` varchar(255) NOT NULL,
  `about_me` varchar(255) DEFAULT NULL,
  `website_url` varchar(255) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `karma` int(11) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vote_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `vote_type_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_votes_on_post_id` (`post_id`),
  KEY `index_votes_on_vote_type_id` (`vote_type_id`),
  KEY `index_votes_on_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
share|improve this answer
    
you still don't have any foreign keys, MySQL 5.6 Foreign Keys –  Malachi Dec 19 '13 at 18:30
    
@Malachi Thanks! You're write it should be there. For my use, the Rails models handle all that automatically. –  Hopstream Dec 19 '13 at 18:46
    
that is like using DreamWeaver to create a website and then wonder why the code looks like spaghetti and the site looks like crap on {anonymous browser[no names cough IE cough]}. write the tables in SQL not in Ruby or at least make sure that the code is correct, before you run it on your Database. –  Malachi Dec 19 '13 at 18:51

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.