June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…index will be created-, but we will get a warning (note severity, to be exact): mysql> ALTER TABLE test ADD INDEX (col2); Query…to find redundant indexes. And second, because in some cases, we may need to have what at first seems redundant indexes…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique‘ (woops!), single column tables, incrementing column names in tables, and tables…

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

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

…a database’s ability to optimize queries for a star schema. A star schema presents some unique challenge to…in the largest table. You can find the individual SSB query definitions in my previous blog post. Test environment These tests were done on a…

Filed Under: Benchmarks, MySQL Tagged With: , , , , , , ,

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

…queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first it has to find…table into 100 partitions, the BTree in each partition would be about 4 levels. A “point query” in a billion-row (non-partitioned) table…

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

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…tables, indexes and so on, information very helpful to have a clear picture of our users are doing in the database. To accomplish our task of finding… queries to SELECT queries and is not always possible to get a perfect conversion so there can be some differences. Be cautious with Unique indexes, …

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

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…a way to find the set of indexes in all_indexes, but not in used_indexes.  These indexes (if our original index statistics are good) are candidates to…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…indexes are easy to find out and to avoid them. How? Just specify the index name and MySQL will avoid to create duplicate indexes: mysql> alter table…UNIQUE. How can I find all those keys? There is a tool in Percona Toolkit that can help you to find all those keys in…

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

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes…queries that are faster than one second (which in a high-performance system is virtually every query.) If it’s not possible to use a…

Filed Under: Insight for Developers Tagged With:

Post: Duplicate indexes and redundant indexes

August 17, 2006 By 38 Comments

…UNIQUE because we want it to be UNIQUE and we create KEY so it it can be used in the queries…query to run as index covered query (retrieve all columns from the index) – such indexes may become way too long to be efficiently used by other queries…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…to find out how to make InnoDB FTS blazingly-fast, but simply to get a sense of how it works compared to…a BIGINT UNSIGNED NOT NULL with a unique index on it, your table doesn’t need to be rebuilt. The most important item to…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts