…Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY…pt-duplicate-key-checker will detect subtle redundancies that are engine-dependent, like redundant suffixes for secondary keys …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for… rows in `title` by a secondary index. But the result was that it created a temporary table to count the movies per production… rows. But by avoiding the temporary table, and making lookups to the `kind_types` table by primary key, the result was a query that…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… SYS_INDEXES This table lists all indexes the table has, secondary as well as the primary. CREATE TABLE `SYS_INDEXES` ( `TABLE_ID`…primary key. The matter is regardless at what position primary key fields are defined in CREATE TABLE…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10, sbtest$I_r100… SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it we have random inserts into SECONDARY KEYS (k), which…_inc PK, but in that case I still will need SECONDARY KEY (hid,mid,id) ). Also please note the transaction produces 4…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…` varchar(101) DEFAULT NULL, PRIMARY KEY (`s_suppkey`), KEY `i_s_nationkey` (`s_nationkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `lineitem` ( `l_orderkey` int… secondary key o_orderdate which results in random scans of the PK to fetch the columns that are not part of the secondary key…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
… table sbtest add z int not null;” which I expected would create table with primary key only copy the data to it and when create secondary key by sort. Looking at innodb_sys_tables and innodb…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…’s start with a table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB… | 0.000040 | | creating table | 0.002176 | | After create | 0.000058 | | copy to tmp table | 18.083490 | | restoring secondary keys | 17.824109 | | rename result table | 0.162041 | | end…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… that means that the secondary keys contains the primary key column. Let’s see the following example: mysql> show create table t; [...] PRIMARY KEY (`i`), KEY `key_name` (`name`,`i`) In this example the index ‘key_name’ includes the primary key so that last column…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… more of lineorder in the buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql… char(10), 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, S_Name…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… works. I have one table with size 13Gb what was filled during about 2.5 years. The table is: CREATE TABLE `link_out104` ( `domain… we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index are random – which…
