StackExchange clone: where should I add my indexes?

Question

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`)
);

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, 2013 at 14:51
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, 2013 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, 2013 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, 2013 at 15:03
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, 2013 at 15:04

Community · Accepted Answer · 2017-05-23 12:40:43Z

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.

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.~~

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, 2013 at 14:49

Hopstream · Accepted Answer · 2013-12-19 17:41:32Z

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;

you still don't have any foreign keys, MySQL 5.6 Foreign Keys — Malachi, Dec 19, 2013 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, 2013 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, 2013 at 18:51

Stack Exchange Network

current community

your communities

more stack exchange communities

StackExchange clone: where should I add my indexes?

2 Answers 2

Comments table

post_types

Posts

Post-Tags

Tag Synonyms

Tags

Users

Vote_Types

Votes

Conclusion

Your Answer

Not the answer you're looking for? Browse other questions tagged mysql database-design index index-tuning or ask your own question.

Hot Network Questions

StackExchange clone: where should I add my indexes?

2 Answers 2

Comments table

post_types

Posts

Post-Tags

Tag Synonyms

Tags

Users

Vote_Types

Votes

Conclusion

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql database-design index index-tuning or ask your own question.

Related

Hot Network Questions