…_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_…find a more comprehensive example here. Running SchemaSpy The syntax below if specific for MySQL…RDBMS out there. java -jar schemaSpy_5.0.0.jar -t mysql …crontab once you’re done. What are some ERD tools …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…MySQL designers know can be improved. But there’s a finite amount of engineer… today is that sometimes you find unexpected differences in performance like this…? Yes, I think what you’re referring to is what I covered as … tables and columns. It turned out this solution was 150x faster for…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…applications out there that use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, we may need to have what…will detect subtle redundancies that are engine-dependent, like redundant suffixes for …
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… define in MySQL, we’ll see that it’s defined as 4096. This, however, is not the true limit. To find out what the actual limit is, we must delve further into the FRM file. What is an FRM file… columns you can create. If you try setting the storage engine to InnoDB however, you will get an error message! The…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL There … table id to find indexes of the table. mysql> select * from …SYS_FIELDS We need to know what fields form the primary key…ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
…-implementing storage engine, is to get rid of–purge–the old versions of the records as they become obsolete. In MySQL 5… 5.5 yet. Let’s test these two implementations and find out what benefits, if any, do the additional purge threads bring. The… to not to penalize further TPS. MySQL 5.6.2 Results Now let’s test MySQL 5.6.2. It is important…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… enough, spin-offs from this work included being able to find out and then test that the ENUM limit of 65,535… how the Storage Engine API is called. But back to what the above strace shows. In MySQL 5.5 you can find in sql…_TYPE. i.e. what storage engine it is. We can ignore the upper limit on number of storage engines for MySQL and understanding the…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed…DEFAULT ”, PRIMARY KEY (`Code`), KEY `Population` (`Population`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; mysql> SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |Â Â…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
… been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month. We’ve written posts… to add. (I subsequently discovered that it is possible to find out more information on InnoDB locks even in the older versions… with extensive examples of how to use them to find out what is blocking what and so on. Note that the InnoDB plugin manual…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…your application, otherwise garbage-in, garbage-out and you risk dropping necessary indexes…) Now I need a way to find the set of indexes in all… Estimating the size of these indexes But, what if we want to see …your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However, this blocks…
