mysql implementation of ORDER command

Question

Sorry for a silly question, I tried query with WHERE clause and ORDER at the end, which actually took very long time to implement, even thought WHERE clause was on indexed field. It looks like Mysql firstly performs ORDER on entire table and after that SELECT, which is not preferred behavior. Am I right? How can I cause it first perform SELECT and then ORDER.

Thanks!

Addendum:

mysql> explain SELECT * 
               FROM `products` 
               WHERE (`products`.user_id = 1111) 
                 AND ( (`products`.`type` = 'type1' ) )  
               ORDER BY products.id DESC 
               LIMIT 1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
         type: ref
possible_keys: index_products_on_user_id, index_products_on_type
             , index_products_on_type_and_status
          key: index_products_on_user_id
      key_len: 5
          ref: const
         rows: 2570
        Extra: Using where; Using filesort

gbn · Accepted Answer · 2012-01-18T10:24:34.930

There are a chapter in the MySQL documentation about ORDER BY optimisation

I'm unable to find a MySQL reference, but for comparison here is the SQL Server logical query processing phases. SELECT has to be done before ORDER BY.

Without code, I'm guessing that you have unsuitable indexes for the query you are running

Edit, after code added

You probably need an index on user_id, type, product_id DESC to cover the both WHERE predicates and the ORDER BY: you have no suitable index currently so a filesort operation is generated.

Also, you have SELECT * which requires an extra lookups and processing to get all columns, no matter what index is used (unless the table is just 3 columns)

score 1 · Answer 2 · answered Jan 17 '12 at 15:23

The link gbn provided is a great place to start. Traditionally it is not best practice to use ORDER BY in anything that will be automated (stored procs, views, etc.) in the database - chances are that's not where the data will be consumed by the end user. The best place to order data is in the final output medium that is used by the consumer.

For example, if you're using MSFT Reporting Services, put a sorting on your report rather than in the query. When the report runs, the sort will happen in the rendering cycle of report generation which happens after the data has already been gathered.

score 1 · Answer 3 · answered Jan 17 '12 at 22:15

1

I suggest that your query is not selective enough, so even there is an index on the field in WHERE it's not used... If you post the query and execution plan, you may probably get a better advice.

answered Jan 17 '12 at 22:15

a1ex07

9,060
3
27
41

mysql implementation of ORDER command

3 Answers3