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… Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> START… accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with… locks can be different. In the following link there is a good source of information: http://dev.mysql.com/doc/refman/5…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
April 7, 2012 By 12 Comments
… MySQL server which will then in turn apply the remaining parts of the WHERE clause l_shipmode in (‘AIR’, ‘AIR REG’) and l_shipinstruct = ‘DELIVER IN PERSON’.” then how do you know it ? i mean, where can…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
… table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in set (5.23 sec…% 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: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 25 Comments
… JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347); +———————————-+ | name | +———————————-+ | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in…is not in where clause at all…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL …update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s…
Post: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 20 Comments
…, which in some cases execute outside-in instead of inside-out as users expect. It’s easy to pick on subqueries in MySQL, so I’ll try to be gentle. The following query will surprise users unpleasantly: select * from a where a.id in… the query in many other cases. NOT IN(SELECT …) queries execute badly, too. (Note: putting a literal list of items in the IN() clause performs…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then MySQL can… way that rows could be returned. MySQL does this by adding the WHERE clause in the query with ‘where 0′. Let’s now look at… 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…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause you will find… if we have function in ORDER BY and exactly the same function is equals to constant by WHERE clause. If course it works for direct constants as well. However if functions are different MySQL is not able…
