… ORDER BY is going by field from the table which is not first in the join order index …MySQL can’t do it at this point. Workaround which can be currently used is separate column which holds reverse… does not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; …
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… index is ascending it does not mean it can’t be scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan could be as fast… see “reverse_timestamp” field in Wikipedia table structure. Loose index scan – Number of years ago when I just started using MySQL I thought…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
…index even if it is not packed. Compressed blocks need to be treated differently. For uncompressed index blocks MySQL… will apply: Forward range/index scan will be fast (read_next) as…order by c limit 1 – forward index scan select c from t1 where c like “%abc%” order by c desc limit 1 – reverse index…
Comment: Full text search for all MySQL Storage Engines
June 27, 2006 By 37 Comments
…indexing will be slow) and the datastorage needed is smaller than with reversed index. Each vector is also splitted in 100kbit chunks in order…reversed index and vectorspace model” ? 
Also, does the implementation of sphinx as plugin for mysql 5.1 mean that mysqls…
Comment: Four ways to optimize paginated displays
September 25, 2008 By 27 Comments
… using “force index” to force retreival by an index on your order column. Even if that’s a lousy index from … this way. From what I can tell, the MYSQL optimizer doesn’t take the limit clause into account…’s faste to sort backwards and reverse your limit. e.g. if a query returns 100,000 rows order by …
Comment: Descending indexing and loose index scan
June 20, 2006 By 6 Comments
… do order by scraped asc, siteid asc it goes very fast, because I have an index on (scraped,siteid), but as of MySQL 5.0.21 there is no descending index possible, so how would you suggest I manage the descending index? I noticed you mentioned the reverse_timestamp hack…
