… Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of… you would see “reverse_timestamp” field in Wikipedia table structure. Loose index scan – Number of years ago when I just started using MySQL… the index covered query you might rather shorten your key to KEY(A,B) to keep index smaller. The good news are Loose index scan…
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… lookup people within single zip I would advice to use index in (zip,age) instead of using this workaround. And… yes…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way… means one index range scan was initiated. Handler_read_next=42250 means 42250 rows were analyzed during this scan. Basically MySQL started scanning Index with age>=18 and continue scanning as soon as it met something…
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…-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5.1/en/index-merge-intersection.html
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… table scan when Index access is better or because MySQL simply does not have execution method to resolve query in optimal way – loose index scan…
Comment: My Innodb Feature wishes
May 9, 2006 By 30 Comments
… Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of…
Comment: How adding another table to JOIN can improve performance ?
August 2, 2008 By 12 Comments
Ryan, With optimizer we always find issues. Some of them are bugs others are just limitations in logic. In this case it is exactly this case – Optimizer could consider and use such plans but it does not do it, as it can’t do hash join, loose index scan etc.
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. [...]
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… however so you do > not have all these problems with loosing data etc. Read more in my > Why MySQL Could be… allows the optimizer to use the indexes to join rows together, potentially preventing a table scan. The trick is to know what… have to fix have number of indexing missing which > requires queries to do full table scans. Funny enough this is often > not…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… however so you do not have all these problems with loosing data etc. Read more in my Why MySQL Could be… be fixed however but do not expect it soon. Use Indexes This item was not in original article, however I think… have to fix have number of indexing missing which requires queries to do full table scans. Funny enough this is often not…
