Announcement

Announcement Module
Collapse
No announcement yet.

mysql get latest records by order by desc without subquery

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql get latest records by order by desc without subquery

    Im trying to get result with below query but its taking around 2min to retrieve

    Code:
    SELECT *  FROM customer e  
    WHERE e.id=324  AND e.g_id IN('x133fv','be6544','e992170','93611c')  
    and e.enrol_id =  (  select e1.enrol_id   from customer e1   WHERE e1.id=324   AND e1.g_id=e.g_id   ORDER BY update_time DESC, posted_time DESC, enrol_id DESC   LIMIT 1 )
    I have index on (g_id,id)
    Is there any other way to get the result via JOIN?
    Last edited by infdude666; 08-13-2014, 09:46 AM.

  • #2
    Is enrol_id unique in the table? If not, then the query could potentially return multiple rows for a given (g_id, id) combination, with the same enrol_id but different update_time and posted_time, which would contradict the effort of the ORDER BY.

    Without fully understanding the business logic, one alternative to the query using JOIN could be something like:

    Code:
     select e.*
      from customer e,
      (select e1.g_id, e1.enrol_id
          from customer e1
        where e1.id=324  
         and e1.g_id IN('x133fv','be6544','e992170','93611c')
       group by e1.g_id
       order by e1.update_time DESC, e1.posted_time DESC, e1.enrol_id DESC
       limit 1) t
      where e.id = 324
        and e.g_id = t.g_id
        and e.enrol_id = t.enrol_id;

    Comment


    • #3
      Yes enrol_id is unique.

      The query you gave dint quite work good. I tried this way though,

      select e.* from customer e, (select e1.g_id, e1.enrol_id from customer e1 where e1.id=324 and e1.g_id IN('x133fv','be6544','e992170','93611c') order by e1.update_time DESC, e1.posted_time DESC, e1.enrol_id DESC) t where e.id = 324 and e.g_id = t.g_id and e.enrol_id = t.enrol_id group by e.g_id, e.id;
      Now my question here is question here is : Will the inner sort guarantee that i get latest record since im doing a outer group by clause. Will anytime mysql change it algo to sort differently inner query join with outer group by.

      Comment

      Working...
      X