… times. There are histograms on how many times each row selected for different θ: 0.5, 0.9, 1.1: The…=d.createElement(s);js.id=id;js.src=”//platform.twitter.com/widgets.js”;fjs.parentNode.insertBefore(js,fjs);}}(document,”script”,”twitter…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x… optimization available in MySQL 5.6 here: http://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html and as… is recommended to turn this off. The query used is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select * from t where i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25… a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT… there is a good source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… how many prepared statements are allocated on server and Com_stmt_send_long_data to see whenever sending long …created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** …
Comment: InnoDB's gap locks
April 6, 2012 By 14 Comments
… transactions will be REPEATABLE READ until the first write (or SELECT FOR UPDATE) happens, at which point the transaction is implicitly… demo of this over on DBA StackExchange: http://dba.stackexchange.com/questions/15854/innodb-row-locking-how-to-implement/15864#15864… it should definitely behave the same if you change the SELECT FOR UPDATE into an UPDATE.
Comment: Moving Subtrees in Closure Table Hierarchies
April 18, 2012 By 7 Comments
… trees. I’m still working on puzzles like how to select two branches independently or the problem you have, which is… DAGs: “SQL Design Patterns” by Vadim Tropashko. http://www.amazon.com/SQL-Design-Patterns-Programming-In-Focus/dp/0977671542/
Comment: Best kept MySQLDump Secret
March 25, 2012 By 11 Comments
… really a secret… Quoting MySQL manual from http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html : “While a –single… them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… from 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html… into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’); select * from tab order by 1; 1 Peter 2 Oto 3…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
