… one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm only works with equi-joins. Now let me briefly explain how hash join…)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE INDEX (i_l_orderkey) where c_mktsegment = ‘AUTOMOBILE’ and c…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key… this mention on the MariaDB IRC channel: There are strong forces to ensure 5.5 GA before percona live in Santa…
Post: Daniel Nichter and Percona join forces on Maatkit
July 25, 2008 By 1 Comment
… Nichter, the author of several very useful MySQL tools, has joined with Percona to continue to improve Maatkit — the “other” toolkit…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
… case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-’, tb2.id) with…(11) column. No matter what I did – forced it to use key, forced a different join order, it did not want to use… that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
…, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B The other part is session variable. If…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… to the old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the… plan no matter how you try and you might be forced to change your application by changing how query is written…
Comment: MySQL Meetups Sponsorship Available
May 13, 2009 By 2 Comments
Maybe you could join forces with Sheeri and her “User group fund”? (http://www.pythian.com/news/2443/user-group-sponsorships) Mark
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… “const” or “system” access type it is effectively removed from join execution (replaced with constants) and so ORDER BY can be… efficiency for any position (as long as it is indexed) Force index if needed In some cases MySQL Optimizer may prefer… chose between better sort or better lookup or use FORCE INDEX to force it to use appropriate index. One more note about…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…-length. One reason denormalization is important is that nested-loop joins on large data sets are very expensive. If MySQL… data warehousing workload. The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick…. But beware of falling into the trap of brute-forcing a solution that really needs to be solved with intelligent…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
…-%y’ ) As EDate, EHC.Hits FROM Entries E FORCE INDEX (DPrivate) LEFT JOIN EntryHitCounters EHC ON (EHC.EntryID=E.EntryID) WHERE E… from Entries FORCE INDEX (DPrivate) where DiaryID=11693 and Private=0 Order By Date DESC LIMIT 1235,20) D LEFT JOIN Entries E USING (EntryID) LEFT JOIN EntryHitCounters EHC USING (EntryID) I wish the force index wasn’t needed.
