… INSERT INTO sbtest (hid, mid, id, k) VALUES ($HID, $MID, $ID, $K); INSERT INTO …selectivity is not good for fast inserts, but it is suitable for range selects…insert-roll-2, command line to run: sysbench –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always opens all partitions, even before thinking about… you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non… main use case for PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… generally other considerations to take into account. He also didn’t answer the ‘Insert‘ part. 1. With a …could easily be quicker on a partitioned table, as Mysql can fire off one thread per partition (at … saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do with …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar_ibfk…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
… how to get Percona Server for MySQL up and running: Insert the SD card into a slot on your laptop or …, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row *************************** Success!: Hello …
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ … them… so inserts into one table would happen with correct value, such as 3000000000, while inserting the same value …
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…: percona1 mysql> insert into percona values (0,’percona1′,’jaime’); Query OK, 1 row affected (0.02 sec) percona1 mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name…) percona2 mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
…: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO… size 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join… will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1 from mutex left…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-…try an INSERT … ODKU? First, recall what’s in our table: (root@localhost) [test]> select * from update_test… in set (0.00 sec) (root@localhost) [test]> INSERT INTO update_test (username, host_id) VALUES (‘foo’,…
