…a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides. Thanks to…to the query where you were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to fetch…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…query to illustrate some search is required, so count(*) can’t be optimized away for MyISAM Tables. To show percents for the…MySQL for reporting quite expectedly starts to become the bottleneck. We use a lot of other black magic to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…the purpose of this benchmark, I have used TPC-H Query #10 and ran it on TPC-H dataset (InnoDB tables) with a…the start of the post that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…To illustrate this, I have performed a number of simple benchmarks. Let’s start with a table containing 4 million rows and one secondary key: mysql…to the default method, because it does not have to do random disk seeks to fetch secondary index pages to the buffer pool. A benchmark is worth a…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…start with some examples. First lets run SELECT Query: —TRANSACTION 12303, ACTIVE 26 sec mysql tables in use 2, locked 0 MySQL…mode IS RECORD …the entries in this index rather than primary key. Lets now see about writes: —TRANSACTION 12304, ACTIVE 3 sec fetching rows mysql…a good practice not to…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…the 768 byte local storage in the record. In that source code tree, InnoDB only needs to store locally a…the MySQL binlog write and the InnoDB log flush with a mutex in 5.1, to implement XA, and switching off the…
Post: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
…query information to the log file, it outputs the query time and lock time as floating-point numerics to microsecond scale: sql/log.cc, MYSQL_QUERY…the times recorded in slow query log tables are rounded down to the nearest whole second, meaning any query…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a…MySQL thread id 30899, query id 100627 localhost root Updating update iz set pad=’a‘ where i=1 *** (2) HOLDS THE LOCK(S): RECORD…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…the data portion of pages so once you started Innodb with innodb_force_recovery=1 you can do the following: mysql…to MySQL server during query mysql> insert ignore into test2 select * from test where id>300; Query OK, 573140 rows affected (7.79 sec) Records…
