… writing a SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL … and reinitializing the sequence after making certain insert/update/delete operations. If you have a dataset …the temporary table, and making lookups to the `kind_types` table by primary key, the result was a …
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…GEN_CLUST_INDEX. It doesn’t matter how the primary index gets created it will have … need to know what fields form the primary key. The matter is regardless at what position primary key …’utf8_general_ci’ NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB; In the…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… review different workload (which actually is closer to what I need). Tables looks like: CREATE TABLE `sbtest1…ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential …selects by `id`. However it will interesting how both InnoDB and TokuDB performs if PK …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a… concurrent clients attempting to modify the same record at once. But what will actually happen to the clients who try to update the same… Example 2, above, how above moving the ‘joined’ column to the users_groups table so we don’t need to update the parent group…
Post: Innodb locking and Foreign Keys
December 12, 2006 By 2 Comments
… which also might be important. So lets talk how locks seems to work with foreign keys in Innodb. I’ve only done a… enough to detect which updates are affecting foreign key relationships. So if you will update any column which is not part of FOREIGN KEY constraint – no foreign key originated locks will happen. However if you update PRIMARY KEY value…
Post: How to create/restore a slave using GTID replication in MySQL 5.6
February 8, 2013 By 7 Comments
… One thing that worths to mention is that if you want GTID support log_slave_updates will need to be enabled in slave… post tends to be more practical, we will see how to create/restore new slaves from a master using GTID. How to set up a… any error like primary/unique key duplication then you can run the pt-table-checksum and pt-table-sync. How to restore a slave…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…for multiple group remains question to check with MySQL Optimizer team 
UPDATE: I just heard … how to make MySQL Optimizer to do what we want to do – Just add yet another table to… select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: Using…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… depends on how much of a tradeoff you are willing to make for …to our InnoDB course: CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, bogus_column char(32), PRIMARY KEY…’eee’); START TRANSACTION; # session1 START TRANSACTION; # session2 # session1 UPDATE child SET parent_id = 5
 WHERE parent_…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
… NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE ) ENGINE=InnoDB… failed to delete all intended rows it was not written to the binary log and consequently not reaching the slave. So how can…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this a…for parallel insert/updates) has pretty much table level locks when it comes to auto_increment … should be stored in optimized sort order to minimize how random they are – there is …
