Shilleh: What if it were AS SellerRanks ON SellerRanks.sellerId=.?

Kwaterski: So that would be a derived table

Hansrote: Lucid: Correlated subqueries are a way to loop over the data in SQL. It’s a performance problem, often. But it can be used.

Tory: So much of my problem is that I don’t understand these things

Gliues: Lucid: Show your complete suggestion. The answer is likely No, but I need to see the exact SQL to comment.

Vandebrink: Its great when you can just tell me that because you’re saying it won’t be performant

Folds: I accept that, but spending 30 mins to write a non-performant query makes me want to jump into a woodchipper

Scholz: Lucid: Sometimes you don’t have a choice. The operation may be slow in any case for your problem.

Triola: It certainly seems that way

Liddiard: Lucid: So. STOP worrying about performance until you have more than one *working query* to compare.

Klepacz: It’s going to take me some time to get an example right so don’t wait around for me but I am going to work on this and will post some of my failed attempts here as I go along :

Odea: Thank you for everything all of you

Petrucco: I know that was my fault for bringing up performance of correlated subqueries.

Keiter: It’s a reasonable warning

Keiter: Xgc: I did tell him the same thing before avoiding dependent subqueries

Keiter: On that note, his self-***igned skill level went from a 5 to a 3.

Tirey: Lucid: Some of these are things with which you will become familiar. Each alone is not difficult. But every time we comment, we seem to include some small concept to which you haven’t yet been exposed.

Keiter: Xgc: on that note, we could come up with some slightly made-up concept too, but it would be a bit cruel

Larue: It’s much easier to just make it up.

Keiter: Xgc: have you heard of the tri-lateral function? It would solve his problem too.

Dizon: Or laterial derived table I just made that up, but it’s in the standard. :

Keiter: Xgc: no, no, a tri-lateral.

Marturano: I’m pretty sure that SQL construct can be used to solve world political problems.

Helzer: I just tried to compile the C++ Connector for MySQL, really guys, how shameless to release a new version that doesn’t even compile due to typo’s in the code :

Keiter: Dupondje: well, this isn’t #oracle – it’s a community channel about MySQL

Keiter: Dupondje: you’d do better to blame the actual developers

Rizza: I am really, really struggling to optimise a very simple query: http://pastebin.com/raw.php?i=YC9gayPu

Matters: The table is ~6.3 million rows, and it’s returning ~146 000 rows

Shely: I’ve gotten it down to 2-ish seconds

Bayle: Using a covering index on the two fields in the WHERE clause

Bingham: Selecting 146k records is your main mistake

Puotinen: Well I can use a cursor to step through them and do the necessary computations

Pesses: But there’s unfortunately no way to turn it into an aggregate query, so I figured just returning the raw data would be the fastest

Bingham: Canbe better to store summaries so you never have to do that, update with triggers

Wedgewood: Ah good idea, archivist

Geisinsky: For some reason that escaped me

Talone: Thanks for the ***istance :

Velasques: Okay so this has the basic stats I want: http://sqlfiddle.com/#!9/8da731/1

Correl: Lucid: Still subquery instead of MAX?

Huntsberry: Salle: I dont want MAX rank!

Maciej: Salle: I want the last 2 ranks for that seller

Correl: Lucid: The first subquery at line 4. does what MAX does

Correl: Lucid: Also why that derived table?

Breedlove: Salle: I started with that query