… test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each …log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true …mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…group by results. What you can think about in this case is removing group by and aggregate functions. Then query would become “select…group by, distinct and aggregate functions are removed (A) – look at number of rows examined divided by…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk… MySQL to 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: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…: mysql> 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…); 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
…the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier…and 2001 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.1 SELECT dest.CityName, COUNT( DISTINCT origin.CityName)…
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…mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
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…%’ group by t.table_schema, t.table_name, i.index_name; Now I can query this view to see my indexes: mysql> select * from…
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: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
…have a lot of numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10 | 1 | | -3 | 1 | | -…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: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…are lots of them: mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt_idx) AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY db, …
