June 20, 2013

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key… Handler_read_key is more or less the same for MariaDB 5.5 when compared to MySQL 5.5, and this does not…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… scan less rows but also because it is index covering query now – it does not need to access the data. It turns… – index entries being sorted by primary key for each complete key value. So when you have index (a) and id is a primary key the real index is… ordering of data by primary key inside indexes they can become significantly affected. Optimizer behavior may be different in different MySQL versions. These tests…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…pct or innodb_old_blocks_time. It does highlight how a data set much …the other secondary indexes have been pushed out of the buffer pool. mysql> select * from …key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key…

Filed Under: MySQL Tagged With: , , , , ,

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

…/mysql/ which will show something like this: table: art/link_out104, index: PRIMARY, space id: 12, root page 3 estimated statistics in dictionary: key… this key about 600MB is empty. This needs a bit of explaining: This does not have as good efficiency as the primary key, but… we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index are random – which…

Filed Under: Percona Software Tagged With: , ,

Post: MySQL VIEW as performance troublemaker

August 12, 2007 By 57 Comments

… to summary table avoid changing any queries directly. Unfortunately it does not work. It is interesting to see EXPLAIN for such…: comments type: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select… key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index *************************** 4. row *************************** id: 2 select_type: DERIVED table: comments type: index possible_keys: NULL key: PRIMARY key…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… | test.t | optimize | note | Table does not support optimize, doing …PRIMARY KEY defined, because in this case InnoDB picks such an index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Why Index could refuse to work ?

September 8, 2006 By 15 Comments

…mysql> explain select * from article force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | id | select_type | table | type | possible_keys | key | key… coversion to the string does not work. Interesing enough …

Filed Under: Insight for DBAs Tagged With:

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…capacity. What does this have to do with InnoDB?  Data clustering. InnoDB’s primary keys define the … Especially assuming that there are indexes other than the primary key, we can shrink the primary key‘s width: create … by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier…

Filed Under: Insight for Developers Tagged With: , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET… me. Why does InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY, I… get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: How to find MySQL queries worth optimizing ?

September 11, 2012 By 6 Comments

…keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | 1 | SIMPLE | a | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | b | const | PRIMARY | PRIMARY…

Filed Under: Insight for DBAs, MySQL
« Older Posts
Newer Posts »