… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity = 1 and l_quantity
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…mysql> select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select count(distinct i) from antest; +——————-+ | count…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
…=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582 | +————+ 1 row in set (1.17 sec) mysql> select count(val2) from fact; +————-+ | count(val2) | +————-+ | 7340032 | +————-+ 1 row in…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…not just for new connections as in MySQL. This is very helpful for measurement… of tmp tables created in memory vs on-disk, percentage of queries that … total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 312 # Exec time 50 4s 5ms…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… number of indexes 7 defined in the MySQL This customer was running Percona Server 5.1 and they got this error on…_INDEXES table, which exists in Percona Server 5.1, but doesn’t appear in MySQL until 5.6 (if the manual is… consider this on a newer version of Percona Server or MysqL 5.6. Another person (I’m looking at you, Baron…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
… million records: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count(*) from paintings where author != ltrim_junk(author); +———-+ | count(*) | +———-+ | 28335 | +———-+ 1 row in…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S 1 19018 19018 0 -1…1.00 [1.0], V/M = 0.02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count… better balance of CPU vs IO utilization as …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
…` ) mysql> explain select * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1… hint. The other problem you may have however is calculating count of matching rows which may be even trickier to slow…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance… famously happens in B-Tree inserts (see InnoDB vs TokuDB for example). This doesn’t mean that… the Amazon RDS instance performed at various thread counts on the OLTP benchmark. Update Vadim and Peter…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… ? Let me show. The query in question is (Q1): SELECT count(distinct username) FROM tracker where TIME_ID >= ’2009-07-20…” inserted not in order. +—————————+ | count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan…
