… select query. A query runs for say 300 seconds and because the resultset is huge, it eventually starts creating a temp table on disk. Now during those 300 second, other ‘innocuous’ queries which perform small insert or updates into completely different table (unrelated to the select…
Comment: Using Flexviews - part two, change data capture
March 22, 2013 By 26 Comments
… problem. In my case, insert into r select null, table_name,action,now() from r; Test it with continue try, until select * from binlog_consumer…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…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…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… there are generally other considerations to take into account. He also didn’t answer the ‘Insert‘ part. 1. With a standard numeric… environment on similar sized tables). What I am really saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do with the table. Removing partitions is easier and…
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… is only (about) 5 levels deep. If you split that table into 100 partitions, the BTree in each partition would be about…
Comment: MySQL Partitioning - can save you or kill you
April 25, 2013 By 45 Comments
… increase in your Selects, as you will be querying on a smaller dataset. You shoould have a faster insert as well, as you will be adding into a smaller dataset/index. Partitions are effectively a ‘chunked’ table, which requires the DB finding out which ‘chunk’ to work on, then inserting or reading your data…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… exception – INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2 table. It also applies to similar tables with…: 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 OUTFILE…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…=MYISAM; Query OK, 0 rows affected (0.03 sec) mysql> insert into test2 select * from test; Query OK, 229376 rows affected (0.91…> insert into test2 select * from test; ERROR 2013 (HY000): Lost connection to MySQL server during query You may think will will scan the table… to MyISAM table. Using series of queries with LIMIT can be handly if you recover manually: mysql> insert ignore into test2 select * from test…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
…; the table was only inserted into (and selected from). Instead, the blocked statements were because of INSERT… SELECT statements that were running against the table, selecting data from it and inserting into another table…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… is our mutex table. We only need to insert one integer value: create table mutex( i int not null primary key ); insert into mutex(i… 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…
