I have id, member_id, topic_id fields. Sometimes I use id, sometimes member_id and sometimes topic_id in WHERE clauses. Can I add Indexes to all of them? Will it make it slower? I am new to MYSQL optimization stuff, so thank you.
views:
43answers:
2
+3
A:
Unused indexes won't make a SELECT slower, but each index you add will slow down INSERTs and UPDATEs.
The maximum number of indexes a MyISAM table can have is 64
Paul Dixon
2010-06-29 08:07:00
Will it make INSERTs and UPDATEs slower a lot or only a little bit? And as I can see you are good in php/mysql, please check this question if you have time: http://stackoverflow.com/questions/3138487/how-many-fields-is-normal-to-have-in-one-table
hey
2010-06-29 08:08:53
Generally, unless you are performing huge updates, it's not worth worrying about. For a large insert you can temporarily drop the indexes and rebuild them afterwards
Paul Dixon
2010-06-29 08:14:09
Also, you can use EXPLAIN to find out if a given query is using the index you expected, and can also help you spot the need for new indexes
Paul Dixon
2010-06-29 08:15:25
+1
A:
In general, you would want a separate index on each field if you will be filtering your queries only on single fields, such as in the following case:
SELECT * FROM your_table WHERE id = ?;
SELECT * FROM your_table WHERE member_id = ?;
SELECT * FROM your_table WHERE topic_id = ?;
If the id field is the primary key, then that is probably already using a clustered index. Therefore it looks like you may want to try creating two separate non-clustered indexes on member_id and topic_id:
CREATE INDEX ix_your_table_member_id ON your_table (member_id);
CREATE INDEX ix_your_table_topic_id ON your_table (topic_id);
You may also be interested in researching the topic of covering indexes.
Daniel Vassallo
2010-06-29 08:08:04