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 12 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 30 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…
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: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… executed without scanning and sorting full result set, so it is important for it to use index – in this case index range scan will…. Index on date_created separately would still work. The good from performance standpoint (even though a bit ugly) will be UNION workaround… some extra filtering takes place so you need to scan more rows by index then requested by LIMIT. However if you’re…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… use an index. This is because we know that the nested loop is effectively going to do a table scan on the inner table — it has to, because there’s no index. If the query joins… << 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. That should…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… only one row while may require to scan hundreds of thousands of rows (or index entries) to do so. Other common killer… different – even if you have index on domain it still can require a lot of rows scanned to provide result set. Such…’ve seen people trying to join all queries in single UNION (with padding to accommodate different types and number of columns…
