… together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT… from performance standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what if you have…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… the fact MySQL is unable to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and… data. Another possible optimizer improvement to do. P.S This post is inspired by Does MySQL Optimize UNION with LIMIT clause topic on our MySQL Forums.
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name… use UNION to avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in… optimization of moving LIMIT inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…are the worst case for performance. Optimal mathematical performance requires operations on … above. If you have any limitation in resources in a cluster (cpu,…, subqueries in the FROM clause, and UNION operations to operate fully in … SQL, but right now only MySQL storage nodes are supported. Amdahl…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL… subselects, inline views, parts of the UNION. This is also common missunderstanding. Works on … query cache option query_cache_limit was implemented which limits result set which can …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…is the best way you can optimize database access. This applies to anything else… SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only one …descr FROM titles ORDER BY rank DESC LIMIT 10 – If there is no …to join all queries in single UNION (with padding to accommodate different types…
Post: MySQL: Data Storage or Data Processing
November 18, 2007 By 9 Comments
…analyzed which often forces us to do tricky optimizations because if query has to analyze 10….data for you in parallel. You may argue limited number of applications needs so serious data crunches -…as sorting, unions/instersections have to be performed on the SQL node. May be MySQL Cluster will evolve…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… and grouping and DISTINCT and UNION may/will also create temp … memory needed to parse and optimize and execute queries; mysqld creates…Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, …things that have no defined limits. It’s not something that…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…- using IN list instead of range or UNION which however require rather serious application …row in set (0.00 sec) mysql> select * from dl limit 5; +————+ | myday | +————+ | 2001-01-01 | …learn about MySQL Optimizer gotchas 
At the same time I figured out how to make MySQL Optimizer …
