索引应该建在那个表mysql,MySQL：我应该使表中的所有列都建立索引吗？

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

解决方案

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.