CODEWITHSUNDEEP
mysqlsql
Sami
Sami

Reputation: 8419

Select values with latest date and group by an other column

See My Table and query here (on sqlfiddle)

Myquery is fetching exact results that I need but it takes long time when data is more than 1000 rows. I need to make it Efficient

Select `id`, `uid`, case when left_leg > right_leg then left_leg
else right_leg end as Max_leg,`date` from
(
SELECT * FROM network t2 where id in (select id from
(select id,uid,`date` from (SELECT * FROM network order by uid,
`date` desc) t4 group by uid) t3) and
(left_leg>=500 or right_leg>=500))t1

Want to pick data from network againt latest dates for each uid Wanted to pick data where left_leg >=500 or right_leg >=500 Wanted to pick only bigger of two legs (left or right)

Whole query might have some problems but Core issue is with this code

SELECT * FROM network t2 where id in (select id from
(select id,uid,`date` from (SELECT * FROM network order by uid,
`date` desc) t4 group by uid) t3)

I want to improve this query because it fetches esults so much slow when data grows.

Upvotes: 1

Views: 1836

Answers (2)

Glenn
Glenn

Reputation: 9170

Taking your description:

  • first find the max date for each uid
  • then find the associate ids
  • filter on the leg values

Example:

SELECT id, uid, GREATEST(left_leg, right_leg) as max_leg
  FROM network
  WHERE (uid, `date`) IN (SELECT uid, MAX(`date`)
                            FROM network
                            GROUP BY uid)
    AND (left_leg > 500 or right_leg > 500)

Note that this means that if the latest date for a uid does not have some legs > 500, then the records won't show up in the result. If you want the latest records with legs > 500, the leg filter has to be moved in.

Upvotes: 2

piotrm
piotrm

Reputation: 12366

Add an index on (uid,date):

ALTER TABLE network ADD INDEX uid_date( uid, date )

and try something like:

SELECT n.id, n.uid, greatest( n.right_leg, n.left_leg ) as max_leg, n.date
FROM
  ( SELECT uid,max(date) as latest FROM network
    WHERE right_leg>=500 OR left_leg >=500
    GROUP BY uid
  ) ng
JOIN network n ON n.uid = ng.uid AND n.date = ng.latest

Upvotes: 2

Related Questions