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.



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
  • 2
    Can you add code please
    – gbn
    Jan 17 '12 at 13:51
  • Also how many rows in the filtered result set and what is the explain plan for both? Jan 17 '12 at 13:59
  • @gbn: I've added the code. One more thing ORDER BY I use together with LIMIT 1 to get the last record, maybe this causes the problem
    – com
    Jan 18 '12 at 7:45

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)

  • 1
    Don't think the DESC will help here. Seems this is another unimplemented feature... Jan 22 '12 at 22:19
  • @Martin: Right :( But I think that on this occasion (that the query has LIMIT 1), it doesn't matter. Jan 23 '12 at 12:40
  • @MartinSmith: I Just noticed that the links to questions, from your profile here in dba site, do not work. (but they work from your SO profile). Jan 23 '12 at 12:43
  • @ypercube - Ah, I used relative URLs. Keep meaning to put something else there anyway. Thanks for letting me know! Jan 23 '12 at 12:48

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.

  • 2
    I'd suggest that a SQL side ORDER BY is more efficient than in the client.
    – gbn
    Jan 17 '12 at 15:44

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.

  • Thanks for the answer, I've edited the post, please take a look at EXPLAIN
    – com
    Jan 18 '12 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.