June 18, 2013

Post: Statistics of InnoDB tables and indexes available in xtrabackup

If you ever wondered how big is that or another index in InnoDB … you had to calculate it yourself by multiplying …, but you may have inexact results). Let’s see how it works. I have one table with size 13Gb what was filled… this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should…

Post: How number of columns affects performance ?

… comes to MyISAM. The common workaround working with such wide tables is to use covering indexes. I added one to t99v1 table… | t99v1 | index | NULL | t1 | 1 | NULL | 12000000 | Using index | +—-+————-+——-+——-+—————+——+———+——+———-+————-+ 1 row in set (0.00 sec) As you can see the index scan… rather expected as table data is stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format…

Post: MySQL Indexing Best Practices: Webinar Questions Followup

… optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B) would work well… 3rd column, say C index (A,B,C) would not work as range will prevent sorting from using the index. In this case… work well. You might need to redesign schema and queries to make it work well. Q: how does a index be used having an index

Post: A case for MariaDB's Hash Joins

… post we will see what the Hash Join is, how it works and for what types of queries would it be the…-most part of the PK in the second table. How does optimizer work with the different Join Algorithms available? Currently, the part… join work best when you are joining very big tables with no WHERE clause, or a WHERE clause on a non-indexed

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

… to find out how to make InnoDB FTS blazingly-fast, but simply to get a sense of how it works compared to traditional….6 manual, and the only page which appears to suggest how using an explicitly-defined column is done is this one… index creation at a time That’s an odd limitation. Do it as two separate ALTER statements, and it appears to work

Post: How adding another table to JOIN can improve performance ?

… does not make sense but this is how it is. I also tried to increase… and it still does not work. The trick however does work if you have just one … Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: …

Post: Find unused indexes

… wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our… INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql> SET GLOBAL userstat=on; After all this hard work we…, you can’t get index statistics usage from those tables. pt-index-usage The previous example only works with Percona Server so with…

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

I have been working with Peter in preparation for the talk comparing the optimizer … is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…

Post: MySQL VIEW as performance troublemaker

… complexity hidden away in VIEW definition. Just another day I worked on optimizing application which uses VIEWs and was looking at… artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from…: NULL rows: 1792695 Extra: Using index *************************** 4. row *************************** id: 2 select_type: DERIVED table: comments type: index possible_keys: NULL key: PRIMARY…

Post: Percona Live MySQL Conference and Expo 2013 – News from the Committee – Tutorial Selection Complete

… all this material in less time. Cookbook for Creating INDEXes — All about Indexing Now for one of my former colleages: Rick James… focuses in on how the query optimizer does what it does, why it does it, and how to make it work for you… into the optimizer and have a solid understanding of exactly how it works. These two talks would be an excellent track for…