111 reputation
3
bio website
location
age
visits member for 1 year, 4 months
seen Apr 15 at 8:47

EAI developer working mainly in Energy and Utilities, Health and Education sectors. Tools of my middleware trade have included webMethods, JavaCAPS, and I'm just about to get my hands dirty in some TIBCO.

Plenty of Oracle and MSSQL thrown about in integration/ETL anger too.

C#.Net is my weapon of choice for all things non-middleware (or at the SOA periphery, where OOTB APIs are non existent, or need a spruce up with something more web-servicey).

I have a history in infrastructure and applications support, which gives me a pretty good sense of the "bigger picture" in terms of how my code plays within the architecture of the wider technical environment, and in terms of how it should play with humans in the support/maintenance sphere when (heaven forbid) my code needs biological assistance.

More recently I've been dabbling in the world of the "front end" on my own time, so am finding my feet in web technologies such as CSS, JS, JQ etc. Am having fun developing (or supporting) sites based on open source CMSs such as WordPress, and learning PHP along the way.


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?