…=OFF performance_schema=0 warmup To warmup server and load data and indices to the buffer pool we use following queries: select avg… –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…), then the query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer…Refreshed both views. Compared the refresh performance and query results. The view type …DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…queries and to catch those which run less than a second as they can also be a performance… REPLICATED STATEMENTS Normally MySQL will not write into slow log any queries executed by the slaveâ…_wait: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest where id …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0…, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… a small fraction of queries to have very different performance, in general — you want consistent performance. Maybe the performance IS consistent, we … them: mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt_idx) AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS queries, …
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… the filter been merged into the view query. As long as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then… a filter on c1, performance is not good. MySQL is scanning the entire table to produce the result: mysql> select * from v2 where c1… (1.66 sec) Even a query with an impossible where clause takes a long time to process: mysql> select * from v2 where c1…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…table has 10000 distinct value, so range 1..100 selects about 1% …mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select… to several tables or perform complex queries finding relationships between …
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
…consists of 5 select queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing these queries will involve notable …may have some correlation with decreasing performance in workloads with MySQL. # Test: POINT_SELECT:throughput, QPS # # Set 1 – …
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…queries: (again look on comparison with other engines in Google Spreadsheet or in summary table at the bottom) Query Q1: mysql> SELECT…query Q4 took 0.75 sec Queries Q5-Q7 skipped. Query Q8: SELECT DestCityName, COUNT( DISTINCT…
