… INDEX `col2`; # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 5 # Total Duplicate Indexes 1 # Total Indexes 3 Additionally, pt-duplicate-key-checker…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… significantly during the hot run, as hash indexes are faster than a b-tree index. Also accessing pages from the buffer pool…. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for… okay because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first… would have to match the order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `child_B_ibfk_1… possible relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…` ( `INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example… SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS | COL_NAME | +———-+—–+———-+ | 1679 | 0 | actor_id | +———-+—–+———-+ Which means the primary key of the table…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=1 –oltp-non-index-updates=0 run
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I…)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it… tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and omitting the corresponding secondary keys from deferred key creation optimization…
Post: MySQL alternative Percona Server 5.1.68 -14.6 now available
April 19, 2013 By 3 Comments
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and omitting the corresponding secondary keys from deferred key creation optimization…
