… test ADD INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… okay because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first… matching rows in `title` by a secondary index. But the result was that it created a temporary table to count the movies… were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to fetch and…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…_table is ON Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL There is yet another source of information… | | 738 | +————–+—–+——–+——+——–+———+————–+——-+ SYS_INDEXES This table lists all indexes the table has, secondary as well as the primary. CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint… unique index InnoDB will create one implicitely. Its name will be GEN_CLUST_INDEX. It doesn’t matter how the primary index gets created it…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10… actually is closer to what I need). Tables looks like: CREATE TABLE `sbtest1` ( `hid` int(10) unsigned NOT NULL DEFAULT ’0… stable, that is why I also show 5-minute moving averages to TokuDB. So TokuDB shows about 2.8x better throughput, and…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… an empty transaction list when creating read views. However, the more general descriptors optimization in Percona Server showed fairly close results in… read-only transactions, start suffering from the trx_list overhead created by concurrent updates. Once we step away from this spherical…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL … key constraint which implied an implicit secondary index with the same name. Fixed by detecting …from MariaDB for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on …
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL … key constraint which implied an implicit secondary index with the same name. Fixed by detecting …from MariaDB for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on …
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
…by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel … now. Bugs Fixed: innobackupex is using SHOW MASTER STATUS to obtain binlog file and… information wasn’t available. Fixed by not creating xtrabackup_binlog_info file when binlog isn…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: …can become very slow when index does not fit to memory any more. Creating Index by sort is great Creating index by sort can provide…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX… got about 178 MB index size after fast index creation as reported by SHOW TABLE STATUS versus 265 MB index size with the optimization… overall process of creating and loading an indexed table by creating the table with only the clustered index, and adding the secondary indexes after the…
