… okay because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first… would have to match the order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On…, I think what you’re referring to is what I covered as the Derived-Table Solution on slides 55-59. Apologies…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… significantly during the hot run, as hash indexes are faster than a b-tree index. Also accessing pages from the buffer pool… may perform differently under concurrency, which this benchmark does not cover. I will make a follow-up post about concurrency in… data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… Flow control. So, I thought I’d write a post covering flow control because it is important to understand. What is… translates into number of unique key lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory. It keeps the certification interval small, which…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…. Here is a brief context recap. There are two cases covered in the first post: single SELECT queries doing PRIMARY KEY…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=1 –oltp-non-index-updates=0 run
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… RANGE (to_days(…)). However, the ranges are uneven — older partitions cover larger timespans. The last few partitions are one week each…. * A ‘range’ delete would mostly follow above two points. * Secondary indexes are also BTrees, so they need updating (although delayed). Yes…/partitionmaint The third use case for PARTITIONing involves 2D-like indexing. I have not found a 4th use case.
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index without reading the row… index which has prefix key parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… between a covering index and a full index scan in the EXPLAIN output. While a covering index (seen with EXPLAIN as Extra: Using index) is a very interesting performance optimization, a full index scan (type: index) is according…
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… data (just one column) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT… the “SELECT note” actually slower? It seems that whenever the covering index technique is available, this is always preferred over the ICP…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… one have optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B… primary key for some queries and use key K as covering index for lookup in other direction. For Innodb Table id2 is…: We have a database that has about 400GB of indexes. The indexes can’t fit in memory anymore. How does this affect…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very good to be able… how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a “row… to use second index and hence index merge, what does it turn to ? It is not combined index but single index on another column…
