…+——+ transaction2> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 FOR … all index records found by the WHERE clause with an exclusive lock and the…en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the …
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
… NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert into t select * from t; I repeated… seconds. Next I ran it with a trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… results, this is naturally an INSERT-only workload. The insertions into the base table from each … which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id,…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
….11 sec) mysql> insert into article_comment values(4300000000,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article_comment values(4300000001,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article… filtering them out because value really does not match the where clause. This makes such error even harder to catch – you will…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… code simple and same for all insert cases – in fact if the insert is single value insert or the number of values in…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also… benefits in MySQL 5.1 It also should be possible to start unlocking rows if they do not match where clause (think for…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…) for table2 table. It also applies to similar tables with where clause and joins. It is important for tables which is being… MySQL Server) as otherwise the script will fail second time. If you need result to be even closer to one of INSERT… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause with ‘where 1′ because… the ‘where 0′ would be generated. Finally, lets insert a few more rows and test the plan: mysql> insert into j1 values (1); insert into…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
…): mysql_connect(“127.0.0.1″, “root”); mysql_select_db(“test”); for ($i = 0; $i < 10000000; $i++) { $b = $i % 1000; mysql_query(“INSERT INTO… using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY… | 75327 | Using where | +—-+————-+————+——+—————+——+———+——-+——-+————-+ 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
… of adding a single new node. When you insert a subtree, you’re inserting multiple nodes, and you want to create new… descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can… AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ AND supertree.descendant = ‘B’; This inserts A-D, B-D, A-E…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr…where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY clauses…line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1…
