… example, putting the UNIONs into a subquery and then doing the SELECT INTO OUFILE from this. Using the employees.employees table: mysql… UNION simply: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * -> FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION ALL…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT… handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL storage engines. This set….02 sec) Each year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1… clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism can’t be added. Q9 is an example of…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… also would note there are some MySQL optimizer restrictions in how well it… A: Typically you do not need all your indexes to be in …index in every table you create? Example: CREATE TABLE user_competition_entry user_id…need to use something like Trick “Unionizing Order by” described in presentation. Q:…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the simple … same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find …subtree paths if it’s larger than just this simple example). We also already have D-D and E-…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…. Lets look at this example: CREATE TABLE `utest` ( `c1` int(10…MySQL 5.0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union…row *************************** id: NULL select_type: UNION RESULT table: type: ALL possible_keys: NULL key…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… OR indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as … to be a one-size-fits-all silver bullet solution. For example, one option would be to simply … in some other way. After all, if an index isn’t there, MySQL can’t attempt to use it…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… with all the data points you need to attack the right query in the right way. But vanilla MySQL does …column 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_…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… materialized view over a union of all the already joined and aggregated data from all the nodes. A single … resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies … the relational algebra rewrites So, here is an example of such a plan for a simple query …
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want to… because some buffers (sort_buffer_size for example) are allocated to their full size immediately…to be needed. And it’s allocated all at once. So a 128M join_buffer… 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 …
