… of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very important to have ORDER BY… example above we did order by by last column, in fact index can be used for ORDER BY if sorting is done by leading column(s). Note… or functions will block index usage for order by. Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… of the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization… or c2=5 order by ord desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: utest type: index_merge possible… with order by efficiently. I’d say this is limitation as for this case you well could be retrieving sorted streams by the indexes…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
…’t be scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for… you really need Descending indexes ? Most typical case is when you want to order by two colums in different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site… challenge choosing proper index configuration as it is hard to add all combinations of restrictions and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when it is possible to use index for…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… index on one column and using order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY the same index must be used for selection for the same table, not other index…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… the same index (A,B) things like ORDER BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use full index for… not select to use index for sort if you sort full table without a limit). However ORDER BY B or ORDER BY A, B DESC will not be able to use index because requested order does not line up with the order of…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… ORDER BY user_id) t WHERE t.parent_id=0 AND t.status = 1 LIMIT 1 Finally, we can use index hints. Index hints… users USE INDEX(user_type) WHERE user_type=2 AND user_id > 2938575 AND parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…* from idxitest where a=100 order by id desc limit 1; +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | id | select_…indexes. If you have query plans depending on Innodb ordering of data by primary key inside indexes…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: index… first_name FROM employees ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: index possible_keys: NULL key…
