…UNION queries, making the latter more performant than the former. Of course you can stil break all this UNION queries…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work… Shard-Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL… are in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… points you need to attack the right query in the right way. But vanilla MySQL does have its limitations, it reports only… 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp… the queries that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… base table. Knowing this, the query result set is treated as a materialized view over a union of all the already joined… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the… minor abstraction modifications in the data access layer. Shard-Query can provide query execution plans based on the relational algebra rewrites So…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…(0.18 sec) The query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | …(DURATION) from the query‘s real response time. If there were, I could add in a UNION to inject another …
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… 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 or j=5 Such query…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… original query (in memory data) takes 1sec. Let’s see how classic 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… case. As the query time implies MySQL is not smart enough in this case to “dive into” the union and add ORDER BY…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… way so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE… you can profile 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> SELECT sql…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… 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… such query with union. I bet it would even be so with age even if it would take some 100 queries in the union…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… examining MySQL configuration, we quite often want to know how various buffer…– it has to, because there’s no index. If the query joins several tables this way, you’ll get several …<< 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. That should…
