mysql - Query takes too long to run -


i running below query retrive unique latest result based on date field within same table. query takes time when table growing. suggestion improve welcome.

select     t2.*     (         select             (                 select                     id                                     ctc_pre_assets ti                                     ti.ctcassettag = t1.ctcassettag                 order                     ti.createddate desc limit 1             ) lid                     (                 select                     distinct ctcassettag                                     ctc_pre_assets             ) t1     ) ro,     ctc_pre_assets t2     t2.id = ro.lid order     id

our able may contain same row multiple times, each row different time stamp. object based on single column example assettag want retrieve single row each assettag latest timestamp.

it's simpler, , faster, find newest date each ctcassettag , join find whole row matches.

this assume no ctcassettag has multiple rows same createddate, in case can more 1 row per ctcassettag.

select     ctc_pre_assets.*     ctc_pre_assets inner join (     select         ctcassettag,         max(createddate)   createddate             ctc_pre_assets     group         ctcassettag )    newest        on  newest.ctcassettag = ctc_pre_assets.ctcassettag        , newest.createddate = ctc_pre_assets.createddate order     ctc_pre_assets.id

edit: deal multiple rows same date.

you haven't said how pick row want in event multiple rows same ctcassettag on same createddate. so, solution chooses row lowest id amongst duplicates.

select     ctc_pre_assets.*     ctc_pre_assets     ctc_pre_assets.id     =     (         select             lookup.id                     ctc_pre_assets   lookup                     lookup.ctcassettag = ctc_pre_assets.ctcassettag         order             lookup.createddate  desc,             lookup.id           asc         limit             1     )

this still use correlated sub-query, slower simple nested-sub-query (such first answer), deal "duplicates".

you can change rules on row pick changing order by in correlated sub-query.

it's similar own query, 1 less join.


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more