…in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query…in set (0.00 sec) In previous versions of MySQL, you can create two indexes with the same columns (in…example: mysql> alter table test add index redundant (col2, id); Query…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…queries only. An additional benefit is that SchemaSpy is command-line driven and builds the html after each run, so I find…examples you can find a more comprehensive example here. Running SchemaSpy The syntax below if specific for MySQL…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed… store an interim result set, and then use that … of “>” and be guaranteed to find exactly one match. But …in a different order from what you specify in the query? The tuple comparison you’re referring to is this example…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings…in OLAP analysis. Each query in each flight (Q1.1 for example) is tested with a cold buffer pool. Then the query… of data in the largest table. You can find the individual SSB query definitions in my previous…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
…in MySQL 5.5) for metadata locks. Now –set-vars is used to set…query-digest 2.2 has fewer options now. Basically, we re-focused it on its primary objective: analyzing MySQL query…example, there are two versions of the DBD::mysql Perl module that have problems. And there are certain versions of MySQL…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
…Set and Executed_Gtid_Set from the SHOW SLAVE OUTPUT as we can see in the example…in MySQL 5.6. Here are the slides from that session. I hope you find it useful: MySQL 5.6 GTID in…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…then the child is an orphan. For example, we have a parent table Foo …); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql…in set (0.00 sec) That shows that the quality control query can find orphans. Because we have a way to generate quality control queries…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…in set (0.00 sec) In this case we actually join 2 tables but because the access type to the tables is “const” MySQL…example by adding combined index. So what is the easy way to see if query is worth optimizing ? – see how many rows query…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… with large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art … for example – ALTER TABLE make take days or even weeks depending on your storage engine and set of … set so you need to re-test your “final design” again with large set. One thing I often find…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…? Why yet another set of MySQL monitoring plugins? … what to monitor in MySQL. Those are only …find it anywhere but in production. The pmp-check-mysql-deadlocks plugin can help you detect such problems, in…example) get the incremental increase in a variable such as Queries and alert on a queries…
