…across all 3 boxes to see if different hardware has any notable impact…sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in…_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = …selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… so It was pretty simple query: select sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv… temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk… do group by a hash of the page instead of page itself: mysql> select sum(cnt) from (select count(*) cnt from performance_log_080306 group by…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… select/group by/join/where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY…_customers’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by customer…) *If you use MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… Year BETWEEN 2001 and 2001 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.1 SELECT dest.CityName, COUNT( DISTINCT origin.CityName) from ontime_fact… existing data sets easily, improving the performance of queries significantly if they use common query features like BETWEEN or IN. It…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… stupid here, and EXPLAIN for this query took 6 min! If you wonder about carriers – EV is Atlantic Southeast Airlines and… diapason of years. SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE Year BETWEEN N and M GROUP BY DestCityName ORDER BY 2 DESC LIMIT 10…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…GROUP BY o_orderkey ORDER BY num_items DESC LIMIT 10; +—-+————-+——-+——-+——————————————–+———+———+——————-+———+———-+———————————————-+ | id | select… each distinct key value,… enabled. If join_…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…!). mysql> create schema index_analysis; If our index_statistics are collecting on… as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select…group by t.table_schema, t.table_name, i.index_name; Now I can query this view to see my indexes: mysql> select…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group by val; Query OK, 9… with this concept. Lets consider that I want to determine if I have certain numbers in my set? First, lets create… sec) mysql> select val, count(distinct id) from data where val in (-2,-3,-10,15,15,16) group by val ; +—–+——————–+ | val | count(distinct id…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P.TABLE_SCHEMA, ‘.’, P.TABLE_NAME, ‘.’, P.COLUMN_NAME, ‘`’) ORDER BY… foreign key constraints from the INFORMATION_SCHEMA, but what if our database includes relationships for which we never declared…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…9 sec – Q8: SELECT “DestCityName”, COUNT( DISTINCT “OriginCityName”) FROM “ontime” WHERE “Year” BETWEEN 2008 and 2008 GROUP BY “DestCityName” ORDER BY 2 DESC; Years, LucidDB…can be easily integrated with Java applications, which is important if your development is Java based. Worth to mention that in…
