MYSQL: Index keyword in create table and when to use it

This keyword means that you are creating an index on column blog_post_id along with the table.

Queries like that:

SELECT *
FROM blog_comment
WHERE blog_post_id = @id

will use this index to search on this field and run faster.

Also, there is a foreign key on this column.

When you decide to delete a blog post, the database will need check against this table to see there are no orphan comments. The index will also speed up this check, so queries like

DELETE
FROM blog_post
WHERE ...

will also run faster.

I'd recommend reading How MySQL Uses Indexes from the MySQL Reference Manual. It states that indexes are used...

• To find the rows matching a WHERE clause quickly.
• To eliminate rows from consideration.
• To retrieve rows from other tables when performing joins.
• To find the MIN() or MAX() value for a specific indexed column.
• To sort or group a table (under certain conditions).
• To optimize queries using only indexes without consulting the data rows.

Indexes in a database work like an index in a book. You can find what you're looking for in an book quicker, because the index is listed alphabetically. Instead of an alphabetical list, MySQL uses B-trees to organize its indexes, which is quicker for its purposes (but would take a lot longer for a human).

Using more indexes means using up more space (as well as the overhead of maintaining the index), so it's only really worth using indexes on columns that fulfil the above usage criteria.

In your example, the id and blog_post_id columns both uses indexes (PRIMARY KEY is an index too) so that the application can find them quicker. In the case of id, it is likely that this allows users to modify or delete a comment quickly, and in the case of blog_post_id, so the application can quickly find all comments for a given post.

You'll notice that there is no index for the email column. This means that searching for all blog posts by a particular e-mail address would probably take quite a long time. If searching for all comments by a particular e-mail address is something you'd want to add, it might make sense to add an index to that too.