…MySQL does have its limitations, it reports only a subset of…operations, the number of unique pages the query accessed, the length of… # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from…WHERE tt.taxonomy IN (‘category’, ‘post_tag’, ‘post_format’) AND tr.object_id IN (733) ORDER…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…distinct UniqueCarrier): 29 1 row in set (0.02 sec) Each year has tens of millions of flights: mysql> select…WHERE Year BETWEEN 2001 and 2001 GROUP BY dest.CityName ORDER BY 2 DESC; — Q8.1 SELECT dest.CityName, COUNT( DISTINCT…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order…DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct…orders: mysql> delete -> from order_lines -> where order…
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
…of 5 select queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing these queries will involve notable amount of malloc()/free() operations…of 32vcpu is ~4x. POINT_SELECT – very simple query – SELECT c FROM sbtest WHERE…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…operating system cache, or whether it’ll have to go to disk. (This distinction…MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… of load. Datasize Size of database …Operating Mode (infinidb_vtable_mode). mysql> show warnings; +——-+——+————————————————————+…SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE YearD BETWEEN 2008 and 2008 GROUP BY DestCityName ORDER…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…. MySQL currently allows you to see many per-session statistics for operations with SHOW SESSION STATUS, but that does not include those of…: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest where id between 501895 and 502895 order by c; or if…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select…
