Re: Large table designissue

[Rick James]

You will have to take the hit to get shrink the BIGINTs, make compound indexes, add the index I suggested, etc. But you only have to take the hit once -- do all the changes in a single ALTER TABLE statement. The big ALTER could also drop some of the unused indexes.

Show me your slow queries; I'll show you the necessary indexes, compound or not. For example:
SELECT ... FROM transactions
WHERE group_id = 123 AND sessionid = 456
would greatly benefit from _either_ of these:
INDEX(group_id, sessionid)
INDEX(sessionid, group_id)
Having both of these would be much less beneficial:
INDEX(group_id)
INDEX(sessionid)

Another:
SELECT ... FROM transaction
WHERE guid='1234asdf1234adsf1234'
ORDER BY transaction_time
would benefit from
INDEX(guid, transaction_time)

If you do EXPLAIN on each of your SELECTs, you will probably find that only a few of the indexes are being used.

Doing an ALTER on the table will first lock the table for a long time on the Master. Then it will lock the table for a long time on the slave.

You mention mass/multiple update -- what do you mean? Are you referring to UPDATE statement(s)? By "limit" are you referring to LIMIT? With an OFFSET? I need more details to answer your question.