Re: Large table designissue
Posted by: Rick James
Date: November 18, 2009 12:15AM

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:

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.

Options: ReplyQuote

Written By
October 24, 2009 02:46AM
October 25, 2009 09:11AM
October 29, 2009 09:36PM
October 29, 2009 10:34PM
November 13, 2009 01:05PM
Re: Large table designissue
November 18, 2009 12:15AM
November 19, 2009 12:38PM
November 19, 2009 12:55PM
November 24, 2009 11:49PM
November 25, 2009 11:23AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.