… (0.00 sec) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt group by c order by null… to linear dependence of table size, and it can get 100 times faster than MyISAM on disk table even in case…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by …by the buffering step introduced by MRR, and hence, there is a slight increase in query time for MySQL 5.6, increase of 0…_custkey i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… -h 127.0.0.1 -P 9999 -…iu.cnt / total_cnt * 100 AS pct FROM…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…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
…cases when upgrading from MySQL 4.0 and 4.1 to MySQL 5.0. By serious I mean …MySQL 5.0 broken group commit bug. First I should note I am extremely unhappy how MySQL handled this problem. While working for MySQL…be good change to do with MySQL 4 also because 100% durable transactions are not required…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…mysql –num-threads=1 –max-requests=0 –oltp-dist-type=uniform –max-time=180 –oltp-read-only –mysql-host=localhost –mysql-table-engine=innodb –mysql…using(index_id) where t.schema=’test’ group by i.index_id \G INDEX_NAME …325 req/sec for sysbench and some 100 seconds for mysqldump which is a …
Post: Sphinx 0.9.8 is released just in time for OSCON 2008
July 20, 2008 By 2 Comments
…used for minor releases) and use version 2.0.0 as a target for full live updates. Though… to what MySQL build in full text search uses) you can get performance another 20-100% better though… seconds using MySQL with best covering index (so no temporary table or sorting is needed for group by). Note…
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
…, the latest error detected. If you have 100 deadlocks per minute you will be able…D=test,t=deadlocks u=root,h=127.0.0.1 mysql> show create table test.deadlocks\G *************************** 1. row … the victim of the deadlock. You can group by server and timestamp to get all events …
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…group=2 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT query_cache_size=0 query_cache_type=0 MySQL…| dbt3.supplier.s_suppkey | 250 | 100.00 | Using where | …-01′ GROUP BY o_orderkey ORDER BY num_items DESC …
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…. This table is partitioned by month which means that MySQL can use partition pruning …:arial,sans,sans-serif;font-size:100.0%;font-weight:normal;font-style:normal;…GROUP BY carrier ORDER BY 2 DESC; .tblGenFixed td {padding:0 3px;overflow:hidden;white-space:normal;letter-spacing:0;word-spacing:0…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…, `Origin` char(5) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, …field. After that load statement is: mysql -S /tmp/mysql-ib.sock -e “LOAD DATA INFILE… GROUP BY carrier ORDER BY 2 DESC [ "WN", 296293 ] [ "AA", 176203 ] … With 0.27s for MonetDB and 0…
