…=/usr/share/java/mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY… possible relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… replication. The buffer pool and adaptive hash index on the standby server will not have … | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –…likely you want it to redirect to /dev/null, and only have a log file for debugging…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS | COL…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… NULL, k INTEGER UNSIGNED DEFAULT ’0′ NOT NULL, c CHAR(120) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX…=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… like CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY K(id2)) engine… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… NULL, INDEX(j), FOREIGN KEY (j) REFERENCES bar(j) ) ENGINE=INNODB; CREATE TABLE bar ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX… ( fkto2 char(32) not null primary key, fkto3 char(32) not null, fkfrom3 char(32) not null, index(fkto3), index(fkfrom3), foreign key (fkto3) references… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` char(25) NOT NULL, `l_comment` varchar(44) NOT NULL, `l… I/O we had during execution of query, as MySQL used index to scan rows and then did access to data to… works. What else worth to mention here is that MySQL is wrong choosing index scan here. If we try to execute query…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously…) NOT NULL AUTO_INCREMENT, `person_id` int(11) NOT NULL, `movie_id` int(11) NOT NULL, `person_role_id` int(11) DEFAULT NULL, `note… you need more manual care and tuning now. MySQL is conservative about “Using index” -in most cases it will be the right…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed…NOT NULL DEFAULT 0, username VARCHAR(20), … other columns here … PRIMARY KEY(user_id), INDEX `parent_id` (parent_id), INDEX `status` (status), INDEX `…
