June 20, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY…

Non-FK relationship, implicit relationship to … flagged ‘nullable’ and ‘must be unique‘ (woops!), single column tables, …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

…single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (…, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first it has to … the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves …

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

…primary keys and foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_…’LineItems’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, REFERENCED_TABLE_SCHEMA = ‘ecommerce’, REFERENCED_TABLE_NAME =…

Filed Under: Insight for DBAs, MySQL

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

… sec) Rows matched: 245760 Changed: 245760 Warnings: 0 mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation…

Filed Under: Insight for DBAs Tagged With: ,

Post: Find unused indexes

June 30, 2012 By Leave a Comment

… on INDEX_STATISTICS table. mysql> SELECT * FROM INDEX_STATISTICS; +————–+————+————–+———–+ | TABLE_SCHEMA …non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`stock` DROP KEY `fkey_stock_2`; — type:non-unique…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

… named “foo”.  Notice that the table has a PRIMARY key and another non-unique key named “guest_language”–this will be important later. As…?  In this example, it is not: a non-unique key is chosen rather than the PRIMARY key.  That signals that the tool has done… there are any bugs in the tool, problems with the MySQL server (privileges, etc.), or other oddities, there is a good…

Filed Under: Insight for DBAs, Percona Software Tagged With:

Comment: ORDER BY ... LIMIT Performance Optimization

November 17, 2009 By 100 Comments

… records. This is how my indexes are set:- mysql> show index from follow; +——–+————+———-+————–+————–+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation…

Filed Under: Insight for Developers Tagged With: ,

Post: Tools and Techniques for Index Design Webinar Questions Followup

October 4, 2012 By 5 Comments

… relevant to current versions of MySQL. Q: How do the upcoming changes in MySQL 5.6 change the… be impossible to partition a table while using UNIQUE KEY constraints the way that makes most sense for… same with a partitioned table as with a non-partitioned table. But since each partition is physically…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…MySQL…direct_non_…key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key_write_requestsblogpercona.commanual Key…commanual unique_checksblogpercona…

Filed Under: Insight for DBAs, MySQL

Post: Percona Server 5.1.59-13.0

October 16, 2011 By Leave a Comment

…). The mysqldump option –innodb-optimize-keys did not work correctly with tables where the first UNIQUE key on non-nullable columns was picked as the clustered index by InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query…

Filed Under: MySQL, Percona Software
« Older Posts