… index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call redundant indexes BTREE indexes… from the index) – such indexes may become way too long to be efficiently used by other queries. There is currently no tool in MySQL distribution which will help you to check your schema for redundant and duplicate indexes. If you…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… | Using where | +—-+————-+———+——-+—————+————+———+——+——+————-+ 1 row in set (0.00 sec) The index on query 3 had high cardinality but should not be… helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not… results, then subtract the indexes that were mentioned from all indexes known. There’s an old tool called mysqlidxchx which should be…
Post: The ultimate tool for generating optimal my.cnf files for MySQL
August 18, 2008 By 34 Comments
… performance penalty. If it were as easy as writing a tool to do this, don’t you think Maatkit would have…’ll ever even feel motivated to try creating such a tool. Don’t bother with scripts. Don’t waste your time… has far less impact on performance than tuning your schema, indexing, queries and — you guessed it — thinking deeply about your application…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed… to see better algorithms, statistics computation and monitoring variables about index usage in the future, now that we have more complex… 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last GA release? Then I…
Post: MySQL Indexing: Best Practices Webinar
August 7, 2012 By 3 Comments
… go over the best practices in selecting correct indexes for your database workloads. Indexing looks like a very basic topic, but it… performance by having your database well indexed, compared to “basically” indexed. In addition to talking about indexes themselves I will also talk about… use to get MySQL to use indexes when it could not before. This is another tool every MySQL DBA should have in…
Post: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 26, 2009 By 10 Comments
… of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated… help FALSE hostname localhost ignore-databases No default value ignore-indexes No default value ignore-tables No default value options-file… default value verbose 0 (out of 2) version FALSE ** FULLTEXT indexes are not taken into account by this patch, so be…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… single index in the table: ~/recovery-tool# ls pages-1329221407/FIL_PAGE_INDEX/ 0-26 0-27 In this case, there are two indexes… itself. ~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-26/ 96M 0-26/ ~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs… in a human readable format. ~/recovery-tool# ./constraints_parser -5 -D -f pages-1329221407/FIL_PAGE_INDEX/0-26/ > data/salaries.recovery…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…*/ SELECT `guest_language`, `guest_country`, `score` FROM `test`.`t` FORCE INDEX(`guest_language`) WHERE ((`guest_language` > ?) OR (`guest_language` = ? AND `guest… about indexes.) … Notice in each SQL statement the clause: FORCE INDEX(`guest_language`) The tool has chosen to use the guest_language index instead… in v2.1.2. Granted, the tool can copy the table using the guest_language index, else it would not have chosen…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… pages-1345219305/FIL_PAGE_INDEX/0-1 LOAD DATA INFILE ‘/root/src/recovery-tool/s_tools/dumps/default/SYS_INDEXES‘ REPLACE INTO TABLE `SYS….SYS_INDEXES -4Df pages-1345219305/FIL_PAGE_INDEX/0-3| grep 15 | grep PRIMARY LOAD DATA INFILE ‘/root/src/recovery-tool/s_tools/dumps/default/SYS_INDEXES‘ REPLACE INTO TABLE `SYS_INDEXES…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
… I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1… which has no secondary index and only a primary index, and against the table purchases_index which has 3 secondary indexes. Another thing I… now there are pros and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have an…
