…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then parallelism… BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping and filtering is used…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… see what UNION can handle what IN can’t: Lets say we want to show people in appropriate age group sorting by time… age=18 order by 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…
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… = dim_date.date_id) WHERE dim_date.Year IN (2008) GROUP BY 1 ORDER BY NULL [1] => SELECT origin_airport_id AS `origin_airport… = dim_date.date_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_index_stats) ORDER BY TABLE_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage’ and see the union of both of…_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES…_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…? A: Typically you do not need all your indexes to be in memory … need to use something like Trick “Unionizing Order by” described in presentation. Q: what is …group by by this column. This works because by scanning data in index order MySQL gets data in already sorted order and looks at “one group…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… so. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If… is not optimally indexed – this is easy. For example our ORDER BY rank query is such – adding index on rank column maxes… with this. I’ve seen people trying to join all queries in single UNION (with padding to accommodate different types and number…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…. Were you rebuilding the MERGE UNION every week? That should have been … ObjectClass in (…) and ObjInstance in (…) GROUP BY ObjInstance) o2 where (o1.ObjInstance = …’2014-06-12 08:13:27′ order by UTCTime desc limit 50000; ” I …_only_ SELECTs you need, then DROP all your secondary keys; they are …
