Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So… started scanning Index with age>=18 and continue scanning as soon as it met something larger than 20. Now let’s see what UNION…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… be done only by reading data from the index. MySQL can ether read index only for all rows, in this case you… cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if… people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people WHERE age=19 AND zip…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can do with index… not to do Index merge (either intersection or union) but instead do full table scan or access table picking only one index on the…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… case the combined index is useless and MySQL has an option of doing full table scan or doing the Union (instead of intersection… | Extra | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… convert sort to the union for small ranges. Q: In the case of a junction table, would indexing on (foreignkey1,foreignkey2) AND… use something like Trick “Unionizing Order by” described in presentation. Q: what is the impact on indexing to use wider UUID such… group by by this column. This works because by scanning data in index order MySQL gets data in already sorted order and…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as we will see below) scan the indexes on indexed_colA and indexed_colB…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we should understand that mysql finds… alone. If you don’t have such compound index then mysql most probably use index on “available” column only using ref access…/10/using-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5.1/en/index-merge…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
… coordinates, date, item type, etc, and the all-time favourite, union from a huge archive table and an active table, plus… BEFORE sorting them. Postgres will bitmap-index-scan on a boolean OR/AND mix of my indexes so it will never even look…
Comment: Using UNION to implement loose index scan in MySQL
August 14, 2006 By 15 Comments
[...] Few days ago I wrote an article about using UNION to implement loose index scan. [...]
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…_time now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change ! thread_cache_size is…_SWITCH VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on…
