… some point. When doing SELECT … UNION SELECT, where do you put the the INTO OUTFILE clause? On the first SELECT, on the last or… bear, otherwise SELECT .. UNION SELECT INTO OUTFILE should be convenient. Lastly to demonstrate the purpose of this post. mysql [localhost] {msandbox} (employees) > SELECT * -> FROM employees…
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 * from… set for the union fully and when only takes 10 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from utest…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
…, having about 40.000.000 of rows select * from test.abc where i=5 union select * from test.abc where j=5 This original query was taking about 22 seconds. As I modified it: select * from… to tell you this shameful fact: mysql> explain (select * from test.abc where i=5) union all (select * from test.abc where j=5…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE age… execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name… UNION to avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… 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 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 22 Comments
… OriginStateName as `StateName` , OriginWac as `Wac` FROM ontime_stage UNION select Dest as `airport_code`, DestCityName as `CityName`, DestState as…there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row …
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
When examining MySQL configuration, we quite often want to know how various buffer … join buffer size.” Here’s the code, in sql/sql_select.cc in 5.1.47 source: 14176 /***************************************************************************** 14177 Fill join… join_buffer_size = 1 << 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. That should try…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
… with the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list… partially: SET @query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA… response time. If there were, I could add in a UNION to inject another row for “lost time” and show the…
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… (0.00 sec) mysql> select * from tm where id=1; +—-+ | id | +—-+ | 1 | +—-+ 1 row in set (0.00 sec) mysql> select * from tm; +—-+ | id | +—-+ | 1 | | 1 | +—-+ 2 rows in set (0.00 sec) mysql> explain select * from tm where…, key(id)) type=merge union(t1,t2); Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> select * from tx where id…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might….1999 2.5% 51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_summary wp_pp….00 0.00 SELECT film # 9 0xCFC0642B5BBD9AC7 0.0987 1.2% 50 0.0020 1.00 0.01 SELECT UNION wp_pp_daily…
