MySQL Forums
Forum List  »  Partitioning

Re: Modify primary key for partitioning
Posted by: Aftab Khan
Date: August 31, 2012 03:38AM

>>I'm thinking about "vertically partitioning" them into another table.
What's the main goal ? Getting a smaller table (without those two huge columns) and so have a cacheable table ?
>What are the consequences for the application ? I guess some queries would need to be rewritten ?


InnoDB stores large texts/blobs separetely from other columns if no more space is available within the same block. The separated page is called "Overflow Page".

So, if your queries don't reference long columns (XMLCERTIFICATE, `XMLPOSTIT`) in SELECTs. For instance, most of the SELECTs (e.g. >50%) don't reference long columns such as XMLCERTIFICATE, `XMLPOSTIT`, then it is good to split this table into two i.e. move long columns into new table.
So yes queries would have to be rewritten to support this change.


>By the way, do you think it's a mistake to set innodb_flush_log_at_trx_commit=2 ? I tried it, the performance was incredibly better (I tried with bulk inserts), but I know that's a bit risky (we can lose 1 second of data).

value 1 is ACID is required for full ACID compliance. Value 2 may give you better performance, and only an operating system crash or a power outage can erase the last second of transactions. Difference is reliability vs performance. If you can afford to lose few transactions in the case of OS or power outage then its probably ok to use value 2.


>The UPDATEs are simple, something like "UPDATE mytable SET column1=x WHERE ID_MSD='value'. It had to do a full scan and so took ages to perform. If needed, I'll create an index either on RECEIVED_DATE or ('REVCEIVED_DATE','ID_MSG').

>> PRIMARY KEY (`ID_MSG`,`RECEIVED_DATE`)
>UPDATE mytable SET column1=x WHERE ID_MSD='value'

Update query would use Primary key, so why would you create index on RECEIVED_DATE or ('REVCEIVED_DATE','ID_MSG')?



Edited 1 time(s). Last edit at 08/31/2012 03:40AM by Aftab Khan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Modify primary key for partitioning
1331
August 31, 2012 03:38AM


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.