Tutorial :Mysql Indexes, how many can I add the most?



Question:

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.


Solution:1

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


Solution:2

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »