… Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using… adaptive hash index is improving performance of InnoDB significantly during the hot run, as hash indexes are faster than a b-tree index. Also… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or… IN-ranges in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked list at…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… build by insertion as insertions happen in the “end” of index tree. I’ve updated column to have bunch of long random… which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… folks arrived at this conclusion a long time before the MySQL world started to come around. There are even tuning utilities…, but seems reasonable based on the structure of a B-tree index, so let’s assume anyway: Hard-to-prove assumption #3… and compare the results to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
…Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance… 400,000,000 rows. This creates a table+index data size approximately twice as big as memory,…to-disk threshold, as famously happens in B-Tree inserts (see InnoDB vs TokuDB for example). This…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… sorted indexes with Innodb (with index built by sort) this is when this should be helpful the most. Q3: Does Innodb secondary Indexes trees… contiguous block of 64 pages, 16 kB each. Each B-tree index in InnoDB occupies two SEGMENTs. One segment is for the… additional throughput? HT: ha_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_concurrency, PLUGIN_VAR_RQCMDARG…
Post: Bug#12704861
November 20, 2011 By 1 Comment
… potentially be a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here the full documentation… the update, the new BLOB pages could have overwritten clustered index B-tree leaf pages that were freed during the update. If… the record update, the index tree would be corrupted. The correct solution is to make the freed clustered index pages unavailable to the…
Post: Detailed review of Tokutek storage engine
April 28, 2009 By 21 Comments
… https://launchpad.net/mysql-patch/mytools What makes fractal indexes so interesting is the amount of IO operations to update index tree is significantly less than for usual B-Tree index. It’s as if Fractal Trees turn random IO into…
