I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… 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
…InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps … gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different….1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 25 Comments
… the answer. “Is there a performance difference between putting the 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…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… is a range clause on the previous key part. So if you have INDEX(A,B) and have a where clause A BETWEEN… same time ? Performing such query MySQL has to build (and do lookups) for all combinations which is 31*10=310 in this case… one. In the real queries you can use table with ids just as we had table of days with a where clause instead…
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… should put to rest any debate as to any perceived performance difference between comma join and ANSI JOIN syntax. They are…
Post: Derived Tables and Views Performance
August 31, 2006 By 14 Comments
Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause. In MySQL 5.0 support for views was added. These features are quite related to each other but how do they compare in… it mean in terms of performance: Query on base table executes using index and it is very fast mysql> select * from test where i…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing…where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause … WHERE clause. If course it works for direct constants as well. However if functions are different MySQL …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… worst case for performance. Optimal mathematical performance requires operations on reduced sets. Keep in mind, that … speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s …with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should … rows in result set matching both conditions and so result will be same as query with “i=5 or j=5″ where clause. I also tried this original query (which uses index merge method in MySQL 5.0): select * from test.abc where i=5…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…performance. You can also use this approach when first key column is not in where clause…
