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