…) supports basic queries which use select/group by/join/where. You may not use sub-queries or any non-deterministic … COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total_…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… or dates can be transformed into one or more discrete sub-ranges Any operation on an entire set is the same… such a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ 1 row in set (0.00…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
… were previously sub second. The changes which got a lot of publicity over years is change of TIMESTAMP output format in MySQL 4… application which uses simple MySQL functionality which is unlikely to be affected. If you’re something like SELECT name FROM users WHERE… to run comparison. The tool will run SELECT queries on both new and old MySQL installations and check result set, explain plan…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…-transactional tables, now you can be locked sub-second. Even with innodb only this can …”FLUSH TABLE WITH READ LOCK”. Namely when a select is blocking the flush from completing and … Percona XtraBackup for MySQL in parallel using all your resources available Downloading from s3 in parallel Parallel …
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…X7542 CPUs giving 24 real cores. The workload was completely in memory in this test. I have compared Percona Server 5.1…range_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…in set (0.36 sec) mysql> select count(distinct i) from antest; +——————-+ | count(distinct i) | +——————-+ | 101 | +——————-+ 1 row in set (0.20 sec) mysql> select…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… negative consequences elsewhere. We could rewrite the query as a sub-select. This will work properly, and it won’t interfere with… with server configuration, altering the table structure, or creating messy sub-selects. In cases where the optimizer just can’t get things right…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…non-distributable ones, can be broken into distributable sub-expressions. What is a result set? This … which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id, count(*), sum(AirTime…
Comment: ORDER BY ... LIMIT Performance Optimization
December 29, 2008 By 100 Comments
… success. Can you help me? In MySQL I do: > create table c (…select a,b from c; So I have: > select count(*) from c; +———-+ | count(*) | +———-+ | 20971520 | +———-+ 1 row in…| Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |…
Comment: Multiple column index vs multiple indexes
August 25, 2008 By 13 Comments
…in set (0.00 sec) mysql> select count(*) from t1000; +———-+ | count(*) | +———-+ | 18000000 | +———-+ 1 row in…
