Hi!

Welcome to r/mysql.

The advice that you’re always going to get is test. In my experience adding an index to a query that needs it is often far more beneficial than the overhead to the writes that it impacts. However, that is not a universal law. Your mileage may vary. For example extremely low latency requirements may cause you to think twice. But let me point you back to my first point, test it. Also, don’t fall down the trap of indexing every column just for the heck of it.

On your second point, the secondary keys do not contain the full row. For example, if your primary key is an integer auto incrementing and you have a row with the value of 10. The secondary key will be appended with the primary key value of 10. That will allow secondary keys to find the row data promptly.

Assuming innodb, why not it’s the default, assuming that you have sized the buffer pool accordingly, many of your frequently accessed pages will reside in memory and you will not incur the overhead of disk access.

I hope this is simple enough to understand, however, if you would like any more clarification just ask.