… –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges… –mysql-socket=/tmp/mysql.sock –oltp-point-selects=9 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS…); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in set (0.02 sec) Each year has tens of millions of flights: mysql> select count…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum…MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values for…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select…of our SELECT. I can now select my droppable (unused) indexes from this view: mysql> select * from droppable_indexes; …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 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: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will not write into slow log any queries executed by… queue or inside that queue waiting for execution. InnoDB_pages_distinct tells the number of unique pages accessed by the query….000000 InnoDB_queue_wait: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest where id between 501895 and…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
… that I have a lot of numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10… pass: mysql> insert into data (val) values (16); Query OK, 1 row affected (0.01 sec) mysql> SELECT val as `val`, COUNT(DISTINCT (id…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… up this query: select sql_no_cache sum(ci.Population) from City as ci where CountryCode in ( select distinct co.Code from Country… stored function. mysql> delimiter // mysql> create function speaks_english(c char(3)) returns integer deterministic > begin > declare res int; > select count(distinct co.Code….Code = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population…
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
…_RO test consists of 5 select queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing these queries will involve…some correlation with decreasing performance in workloads with MySQL. # Test: POINT_SELECT:throughput, QPS # # Set 1 – 5.5.30pre-…
