… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… a MRR range scan is performed, but we can see its only incremented in MariaDB 5.5 and not in MySQL 5.6… incremented when index lookup is performed. As I explained at the start of the post that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations… note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on… Join and Key-ordered Scan enabled, and MariaDB with Hash Join and Key-ordered Scan disabled, and the only difference in query time…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… done only by reading data from the index. MySQL can ether read index only for all rows, in this case you will see “Using Index” in… point. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… scans a lot of rows and completed in about 290ms So we can see index merge indeed improves performance compared to single index only but it is by far better to use multi column indexes. But the problems with Index Merge do not… index on the second column we get a full table scan: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… accessess we might be better of doing full table scan even if only few percent or rows are accessed. Lets do some… scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None… databases. Designing the indexes for MySQL you should only make sure queries use “=” for all keyparts in the last of index. Only last one is… even implemented in MySQL 5.0, but only for very narrow case of aggregate queries. In general complete loose index scan implementation is one…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…=5 for the same index MySQL will use the index… but it will only use A prefix for row lookups and scan whole A BETWEEN… the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN output. These are the main “core” use for indexes…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… would use single index range scan. The 2 indexes however benefits to Q3 because it can only use first keypart of index (j,i) to… cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table scan is performed and… MySQL may decide not to do Index merge (either intersection or union) but instead do full table scan or access table picking only one index…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…) MySQL Optimizer considers using both (a) and (a,b) indexes and in the end decides to use neither rather doing full index scan until… it estimates it will scan 2247 rows in the selected plan, while using (a) index you can get result scanning only 1 row guaranteed. To really get things going you will need to use FORCE INDEX(a) to force MySQL optimizer…
