… | +———————————-+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19…=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…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
… class should be safe. The OdbcParameter abstracts query parameters in order to make sure you can use ODBC in a database… the digital library subscription services? You can purchase my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming in digital formats… SQL reserved words? If we did that, how would we store the legitimate name of a Mr. Order, who lives at 123 Union…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… test. This VM was rebooted between tests. A SQL script was fed to the run_query.php…or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are …Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… result set is treated as a materialized view over a union of all the already joined and aggregated data from all…. This allows BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set processing is…) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index_stats) ORDER BY TABLE_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage’ and see the union of both… (0.02 sec) From here I can use some clever SQL to generate the precise ALTER TABLE statements to drop these…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… mysql> source list.sql +————–+————+—————–+—————–+————-+———————–+————————+ | table_schema | table_name | constraint…b.referenced_column_name UNION SELECT table_schema, …_name, referenced_column_name ORDER BY table_schema, …
