Q:

StackExchange clone: where should I add my indexes?

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

A:

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.

