MySQL 5.5 introduces "columns partitioning".
I'm trying to better understand how it works when two columns are important individually.
Let's say for a table which holds the messaging between two system users. We would potentially have "sender_id" and "receiver_id" columns, and we might want to query against these columns individually.
If we have separate indexes on both of these columns, we can query them individually when necessary. Results are fast.
But what if our table is 100M rows large, and we consider partitioning. My understanding is multiple column partitioning focuses on the first column in the columns definition, then the second one. Here is a sample table structure:
CREATE TABLE messages ( message_id INT, sender_id INT, receiver_id INT ) PARTITION BY RANGE COLUMNS(sender_id,receiver_id) ( PARTITION p0 VALUES LESS THAN (10,10), PARTITION p1 VALUES LESS THAN (20,20), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
If we query for "WHERE receiver_id=5", partition pruning will not kick in, right? It will need to search all partitions. But if we were to search for "WHERE sender_id=5", then we would immediately know the result is in p0.
So for a table where two columns could potentially be individually important, partitioning might not be the best solution since now we lost the benefit of a full-table index for the secondary column(receiver_id, for this case) in the columns parameter. Is that right?