Re: Modify primary key for partitioning
Posted by:
Rick James
Date: August 31, 2012 09:39AM
> I meant only the PK. All the tables have at least a PK, but I think some of them would appreciate some indexes.
It depends on the SELECTs. Sometimes the PK is totally sufficient; sometimes it is not.
> UPDATE mytable SET column1=x WHERE ID_MSD='value'
That WHERE clause begs for an index (PK or otherwise) _beginning_ with ID_MSD. (This is independent of PARTITIONing, etc.) AUTO_INCREMENT also requires such.
> but the queries rarely include both RECEIVED_DATE AND ID_MSG columns.
Then we have been focusing on the wrong things.
> The insert were 3 times faster with only the PK.
Yep.
> 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).
"Mistake" - no. "Risk" - yes. It's a tradeoff; I cannot decide for you. (See Aftab's comments.)
> The one on the MariaDB 5.5 is partitioned...
That adds a wrinkle in the performance comparison.
> Partitioning should slow down a full scan
Not by much. "Count the disk hits". A full table scan will need to read the 'same' number of disk blocks, whether partitioned or not.
> but half of the rows have a received_date equal to '0000-00-00 00:00:00' so I get an unbalanced first partition.
Carry that a step further -- make the first partition so 'old' that it contains only those dates. Something like VALUES LESS THAN '1999-01-01'.
> "vertically partitioning"
Yes, there would be changes in the application.
By moving bulky things out of the main table, long queries that don't need those columns can scan the table faster.
Is it worth doing? Hard to say without thinking about all the queries, both from a recoding point of view, and whether the change would help or hurt the queries. On that topic...
> InnoDB stores large texts/blobs separetely from other columns...
Yes. In different ways depending on which version (Antelope, Barracuda, ...) you are using. Because of this, the "vertical parititioning" suggestion be useless. (For MyISAM, it is often a big win.)