… the performance of queries against a MySQL database by distributing the work over multiple …clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features…2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping …
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… use 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 * from people where age=19 order by last…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… complicated conditions you will need to use something like Trick “Unionizing Order by” described in presentation. Q: what is the impact on indexing… for 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 at…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed…which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law….date_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT *…PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC; +———————-+———-+——-+——-+————–+ | STATE | Total_R…were, I could add in a UNION to inject another row for “…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats) ORDER BY TABLE…UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR …group by t.table_schema, t.table_name, i.index_name; Now I can query this view to see my indexes: mysql…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… mysql> source list.sql …GROUP BY a.table_schema, a.table_name, a.constraint_name, a.constraint_type, b.referenced_table_name, b.referenced_column_name UNION…GROUP BY table_schema, table_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name ORDER BY…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…stick with MyISAM) 1500M. See http://mysql.rjweb.org/doc.php/memory For…. 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 …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will… killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT…trying to join all queries in single UNION (with padding to accommodate different types and…
