Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
4
Impact
~2k people reached

  • 0 posts edited
  • 0 helpful flags
  • 3 votes cast
Sep
24
awarded  Autobiographer
Oct
22
comment How should I best name my timestamp fields?
"at" can imply location too. What about "when" instead of "at"?
Apr
12
comment Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
@AlfredBaudisch so do you agree that my query is returning the rows you need, but with extra rows that you don't want when order_of_arrival=1? And if so, how quickly does it run?
Apr
12
comment Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
@AlfredBaudisch Ok, so we do need to exclude those extra rows, understood. I'm not clear on if your comments relating to ORDER BY date_from ASC and the jump to 400ms was related to my version of the query or yours... So do you want me to persist with making my version return the correct rows (ie did my version perform well)? I don't think it'll be hard to join back on itself to get the single best row for when order_of_arrival=1... which would be an explicit approach to getting it, rather than relying on GROUP BY to continue automagically returning the right one.
Apr
12
awarded  Teacher
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 1234 characters in body
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 778 characters in body
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 778 characters in body
Apr
12
comment Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
@AlfredBaudisch mate, your GROUP BY clause in the second query is causing you to loose rows. You shouldn't be grouping as you're not aggregating anything there. Also, the union is not required as we can just add the logic from each sub query into to a single one. Have added an answer that I believe corrects your issues.
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 290 characters in body
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 632 characters in body
Apr
12
awarded  Supporter
Apr
12
awarded  Editor
Apr
12
revised Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?
added 48 characters in body
Apr
11
answered Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?