MySQL: should I make all columns in my table indexed?

Question

I have a pivot table, well of course every row will be included in a query:

mysql> select * from blog_posts as bp 
       join blog_joins as bj 
       on bj.post_id=1 
       and bj.taxonomy_id=10
       and bj.type = 1;

Here's my table structure:

Is it recommended to make an index for each column? If not, why and what would you recommend?

mysql > alter table blog_joins add index pid (post_id);
mysql > alter table blog_joins add index tid (taxonomy_id);
mysql > alter table blog_joins add index tp (type);

Answer 1

For that specific query you would probably benefit from a multi-column index:

alter table blog_joins add index pid_tid_tp (post_id,taxonomy_id,type);

I'd recommend profiling your code. Try adding realistic data to your test database and try out a few different indexes. Use EXPLAIN to see which indexes MySQL actually uses for each query.

Once you have finished testing remove any unused indexes because while indexes can speed up queries, they will also slow down modifications to the table.