… by ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by ord desc limit 10 \G…: mysql> explain (select * from utest where c1=5 ) union (select * from utest where c2=5 ) limit 10 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… moving LIMIT inside of union clause being cool thing. But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT…, 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…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… last_online desc limit 10) UNION ALL (select * from people where age=19 order by last_online desc limit 10) UNION ALL (select * from people where age=20 order by last_online desc limit 10) ORDER BY last_online desc limit 10; +—-+————–+————–+——+—————+——+———+——-+——-+—————-+ | id | select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… `StateFips`, OriginStateName as `StateName` , OriginWac as `Wac` FROM ontime_stage UNION select Dest as `airport_code`, DestCityName as `CityName`, DestState as `State… total data. then spread over six servers). Due to ICE limitations, the data was split before loading. The splitting/loading process…
Comment: When the subselect runs faster
April 17, 2010 By 9 Comments
… ( select id from table where col1=’a’ union select id from table where col1=’b’ )ORDER BY id DESC LIMIT 20 or even: select t1.* from table t1, ( select id from table where col1=’a’ union select id from table where…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… (even though a bit ugly) will be UNION workaround I already wrote about. So what …is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | possible_…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… DESC LIMIT 10; 10 rows in set (0.31 sec) (SELECT * FROM utest WHERE c1=5 ORDER BY ord DESC LIMIT 10) union (SELECT * FROM utest WHERE c2=5 ORDER BY ord DESC LIMIT 10) ORDER BY ord DESC LIMIT 10 10…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
February 6, 2009 By 14 Comments
… union operation for about 1000 Queries to query for about 20 millions of total records. I mean, about 1000 times. (SELECT …….. LIMIT 15) UNION ALL (SELECT ……… LIMIT 15) . . ORDER BY CREATED LIMIT 15 Or do you advice me to…
Comment: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 19, 2007 By 10 Comments
Peter, Sorry I made a mistake. Finally the query (SELECT * FROM utest WHERE c1=5 ORDER BY ord DESC LIMIT 10) union (SELECT * FROM utest WHERE c2=5 ORDER BY ord DESC LIMIT 10) ORDER BY ord DESC LIMIT 10,takes only 0.03
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… increased to 2000 from 300. Good change considering open_files_limit was also raised. Unlikely to cause significant gains unless opening… switches which you can play with: mysql [localhost] {msandbox} (test) > select * from var55 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row…_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost] {msandbox} (test) > select…
