… way so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE… queries to see what exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache… enable query_cache_wlock_invalidate=ON. Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… pre MySQL 5.0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union (select…. 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: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
One little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use… can give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…incremental materialized view optimizations can be applied. Shard-Query works only on sets Shard-Query takes relational…, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed…which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s …
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking permissions | 0.000005 | … snip … | init | 0.000021 | | optimizing… query‘s real response time. If there were, I could add in a 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… having optimization of moving LIMIT inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is… as query with “i=5 or j=5″ where clause. I also tried this original query (which uses index merge method in MySQL…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what …indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has…in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will …several queries, assuming this query would not need to analyze much more rows because it is optimized … seen people trying to join all queries in single UNION (with padding to accommodate different types…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…of those per query. Sorting and grouping and DISTINCT and UNION may/will …optimize and execute queries; mysqld creates internal structures for the query plan, for example, and keeps it around until the query…mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql…
