… NULL, PRIMARY KEY (`transactionid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION… NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The… information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… the secondary key values by PK column and then performing the lookup, and then there are other possibilities like InnoDB doing read… 2.2G Innodb_data_reads 329115 355323 143808 335526 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read… two other most important numbers are values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it…` char(255) NOT NULL, `i` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs… SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
…, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key_len…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
…-partitioned InnoDB tables is to sort by the partition rule, then by the primary key. The “partition rule” is usually mod(partition_key, ), so we found a good ordering is insert into target-tab … select from source_tab … order by mod(target_partition_key, ), target_primary_key; This will load your partitions one-by-one, and…
Comment: How to recover a single InnoDB table from a Full Backup
April 11, 2012 By 7 Comments
Hi, consider this comment about foreign key and transportable tablespaces : “To export tables that are in a foreign key relationship you must export and import all the tables together” Source : http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections… *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0 AVG_ROW_LENGTH… *************************** SESSION_ID: 7231 TABLE_SCHEMA: test TABLE_NAME: z ENGINE: InnoDB NAME: #sql516_1c3f_0 TABLE_ROWS: 0 AVG_ROW_LENGTH…
