Thesql implementation of ORDER command

MySQL

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

Best Answer

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)

Related Question