…, and reinitializing the sequence after making certain insert/update/delete operations. If you have a…of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On…id. It was more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication? It’s …metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups SET last_joined=NOW(), last_user_id=100 WHERE id=1; COMMIT…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…commanual large_page_sizeblogpercona.commanual large_pagesblogpercona.commanual last_insert_idblogpercona.commanual Last_query_costblogpercona.commanual lc_messagesblogpercona.commanual lc_…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
… before the statement. MySQL uses the INSERT_ID variable to store that value. Binary Log: #111214 0:03:04 server id 1 end_log… id 1 end_log_pos 1245 Intvar SET LAST_INSERT_ID=2/*!*/; #111214 0:04:50 server id 1 end_log_pos 1273 Intvar SET INSERT_ID=3/*!*/; #111214 0:04:50 server id…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_id‘,’customer_id…mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id(‘demo’,'dashboard_customer_sales’)\G *************************** 1. row *************************** mview_table_id: 27 mview_id…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…_test; +—-+———-+———+———————+ | id | username | host_id | last_modified | +—-+———-+———+———————+ | 1 | foo | 3 | 2012-10-05 22:36:30 | +—-+———-+———+———————+ Nothing crazy here, right? We’ve inserted one… going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value is available…) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES (‘foo’, 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW(); Query…
Post: Stored Function to generate Sequences
April 2, 2008 By 17 Comments
… int begin update seq set val=last_insert_id(val+1) where name=seq_name; return last_insert_id(); end // delimiter ; CREATE TABLE `seq` (…one or more sequences, which also can be helpful: mysql> select seq(‘one’),seq(‘two’),seq(‘one’),seq(‘one’); +————+————+————+————+ …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… here that the ALTER waits only at the last stages when its making changes to the table … have reported a bug: http://bugs.mysql.com/bug.php?id=67647 Metadata locking behaves differently for queries …Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null); Query OK, 1 row affected…
Comment: Edge-case behavior of INSERT...ODKU
October 31, 2012 By 6 Comments
… also highlights what would be a VERY good feature in MySQL. InsODKU would be much better implemented on the server side… can be inserted and what can be updated, and gaplock the appropriate number of autoincrs. It would also mean that last_insert_id should return an array of inserted ids, instead of only the last one, like it does now…
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
…, state = Query_arena::STMT_CONVENTIONAL_EXECUTION}, id = 0, query_strip_comments = {buffer = …lex = 0x33ae900, query_string = {string = { str = 0x7f3e84004ba0 “INSERT INTO testdb_N . t1_…Last Executed Statement was found in an optimized core dump by checking the contents of the thd array in the mysql…
