CODEWITHSUNDEEP
mysqlindexing
good_evening
good_evening

Reputation: 21759

Mysql Indexes, how many can I add the most?

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.

Upvotes: 3

Views: 96

Answers (2)

Paul Dixon
Paul Dixon

Reputation: 301135

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

Upvotes: 4

Daniel Vassallo
Daniel Vassallo

Reputation: 344581

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.

Upvotes: 2

Related Questions