indexes slow the updating of the content of the data because the indexes need to be updated as well as the content.
indexes speed up the reading of the data by providing quicker ways of locating what is wanted.
the cost of having or not having an index depends on the ratio of the time saving for reads that are speeded up by it compared to the extra time required by updates that are slowed down by it.
So whatever indexes you add that will speed up those lookups will be making things faster. You only need the ones that are actually going to get used though.
if slug and name are supposed to be unique, you aren’t going to touch them at all
imagine ensuring their uniqueness in some other way – you’ll end up doing one or two SELECTs before the INSERT, to see if the values are already in the table, and then of course you’ll have to wrap them in a transaction to prevent race conditions, etc.
you ~could~ get rid of the id, and use either slug or name as the PK