… my complete answers: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance… rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that… about which query patterns the optimizer recognizes, and which are left to the developer to refactor manually. There could also be…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable…_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on 1 row…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
August 23, 2007 By 22 Comments
… a little weard:P While this works when performing LEFT JOINS, with INNER JOINS it’s a different thing. Let’s say we… the above example, but let’s perform an innerj join instead… The first subquery will return the requested 10 results, and asuming… rows that don’t match some rows returned by the subquery, then the result will be less than 10 rows…
Comment: ORDER BY ... LIMIT Performance Optimization
July 17, 2008 By 100 Comments
… ‘cos that subquery extracts only one post_id – see LIMIT x, 1) SELECT * FROM forum_posts AS pa LEFT JOIN forum_posts_text AS pb ON pa.post_id = pb.post_id LEFT JOIN forum_users ON user…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
…more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will … the blog, naturally containing user_id which left comment, comment_id and comment text: CREATE TABLE `comments`… which are fully populated and “full join” used to to join between them. In this particular …
Comment: Why MySQL could be slow with large tables ?
July 6, 2007 By 141 Comments
… complex and involves a quite a few joins (due to the normalisation) and multiple subqueries (due to nature of the data). (This… PC) (select a, join b, where c…) eg: select a.a, b.x, etc.. from (select … .. ) a left outer join (select … ) b on b.a = a.a etc.. (in total I have like close to 11 subqueries joining a min…
Comment: Multi-Column IN clause - Unexpected MySQL Issue
October 18, 2008 By 21 Comments
… query and then it executes the inner subquery. To bypass this problem I used JOIN and the execution time reduced drastically from… possible solution EXPLAIN SELECT url FROM 106pages.106pages as t1 left outer join 106pages.106pages as t2 on t1.url = t2.url… t2.url_crc IN ((2752937066,3799762538); I have used self join in this case. I haven’t executed the query because…
Comment: MySQL Limitations Part 3: Subqueries
October 25, 2010 By 21 Comments
It seems to me that many common subqueries can be transformed to joins very easily. The difficult cases can be left aside for the moment. Why not transform the simple cases?
