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.