… (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people… column is not in where clause at all if it has just few values. For example if we would have gender instead…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… queries going further lengths to the same effect. For example, putting the UNIONs into a subquery and then doing the SELECT INTO… ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO OUTFILE… ’1990-01-02′ -> UNION ALL -> SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND ’1990-01-06′ -> UNION ALL -> SELECT * INTO OUTFILE…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… speaking about index merge optimization. Lets look at this example: CREATE TABLE `utest` ( `c1` int(10) unsigned NOT NULL, `… 54268 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key: NULL key_len: NULL …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… `StateFips`, OriginStateName as `StateName` , OriginWac as `Wac` FROM ontime_stage UNION select Dest as `airport_code`, DestCityName as `CityName`, DestState as… clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism can’t be added. Q9 is an example of… chance to review this document before publication, as a courtesy. All findings are represented truthfully, transparently, and without any intended bias…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…-D. This makes it easy to query for all descendants of A, or all ancestors of D, or many other common queries….length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the simple case…-subtree paths if it’s larger than just this simple example). We also already have D-D and E-E…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… in sorted order. For example when you query AGE… the row pointers from all indexes, intersect them and … 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… as a materialized view over a union of all the already joined and aggregated data from all the nodes. A single temporary table…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is… based on the relational algebra rewrites So, here is an example of such a plan for a simple query with aggregation…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… This matters because some buffers (sort_buffer_size for example) are allocated to their full size immediately as … determined to be needed. And it’s allocated all at once. So a 128M join_buffer_size …; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…1325146286; select count(*) from auto_inc; Note that logging all queries in this fashion as opposed to the … as that gives us more accurate understanding. Now, for example it is shown here that every query is reading…5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp…
Comment: What does Using filesort mean in MySQL?
October 18, 2011 By 27 Comments
Here’s a trivial but interesting example: EXPLAIN SELECT 20 AS C UNION SELECT 10 AS C ORDER BY C id select… No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL…
