…selection only work when the IDs for movies are distributed uniformly over the range 1..MAX…different order from what you…Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill…by a secondary index. But the result was that it created a temporary table to count…` table first, grouping by kind_id in…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…quite limited. Namely, it… SELECTs followed by a…group = 2 innodb_file_per_table = true innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 max…select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…select count(*) cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select… +————————+———+ | Variable_name | Value | +————————+———+ | Key_…global max…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… really matters is amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only… do so. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If there… column maxes this query to use 10 rows to return 10 rows – exactly what we’re looking for. However our COUNT(*) query…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… shows Name: ontime Engine: InfiniDB Version: 10 Row_format: Dynamic Rows: 2000 Avg_row_length: 0 Data_length: 0 Max_data_length… GROUP BY Origin ORDER BY c DESC LIMIT 10; 4.96 sec Query Q4: mysql> SELECT Carrier, count(*) FROM ontime WHERE DepDelay > 10 AND YearD=2007 GROUP BY…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…count(*) as total_lines from orders o join customers c using(customer_id) join order_lines ol using(order_id) group by…select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by customer_id ‘> order by…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…) group by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_…GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count…FROM `aggregation_tmp_74082863` GROUP BY 1 –POST EXEC: DROP TABLE IF EXISTS `aggregation_tmp#74082863` ; Edit *MIN/MAX…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…s_suppkey` int(11) NOT NULL DEFAULT ’0′, `s_name` char(25) DEFAULT NULL, `s_address` varchar…by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey GROUP BY… sense that it always uses the max values join algorithm. If join_cache_…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ … enter InnoDB is limited by innodb_thread_concurrency (…= 8M innodb_log_files_in_group=2 innodb_log_…max_dirty_pages_pct=90 max_connections=3000 query_cache_size=0 skip-name…
