(Note: when I’m talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.)
I’ve heared an interesting aproach of using Master-slave replication in MySQL.
Thesis
So the theory was that since updates by primary keys are fast and by secondary keys are slow the slave has to be queried for the primary key and then run the updates by the fetched primary keys. To make this in context and more understandable:
Original query
UPDATE table_for_test SET value_to_change = 123 WHERE cond_column_1 = 987 AND cond_column_2 > 765;
This query get splitted to two different query. First query has to be run on the slave to fetch the primary keys:
SELECT pr_id_col FROM table_for_test WHERE cond_column_1 = 987 AND cond_column_2 > 765;
When we have the values we can go to the master and update the necessary records. …
[Read more]