Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
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? –  Martin Smith 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

3 Answers 3

up vote 4 down vote accepted

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)

share|improve this answer
1  
Don't think the DESC will help here. Seems this is another unimplemented feature... –  Martin Smith 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. –  ypercube 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). –  ypercube 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! –  Martin Smith 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.

share|improve this answer
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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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