Correl: Lucid: . WHERE foo LIKE ‘bar%’ and . WHERE foo LIKE ‘%bar%’ are two different queries for which the optimizer can choose very differen plan
Bakerville: Lucid: SELECT a.name, b.name, IFNULLb.cnt, 0 AS cnt FROM tblname AS a LEFT JOIN SELECT name, COUNT* AS cnt FROM tblname2 GROUP BY name AS b ON a.name = b.name;
Dupray: Keiter: is b the derived table?
Keiter: Lucid: it *is* a subquery, but you build your proper aggregates in it.
Correl: Lucid: Another problem is the line 131. That’s join condition in WHERE clause which is always bad practice, but in your case you have outer join which makes it even worst
Hitchingham: Keiter: is the intent the only thing that earns it that name though? it isn’t a semantic for example using something from the parent/outer query within which I guess is a “dependent” subquery?
Correl: Lucid: You are mixing , with explicit join Risse. Better stay with the latter. FROM r INNER JOIN ON . s LEFT JOIN abbr ON .
Durette: Keiter: obviously b in the example lacks that charastic, I was just using it as an example of something other than simple intent
Keiter: Lucid: no, it’s not a dependant subquery.
Keiter: Lucid: you don’t want to use dependant subqueries.
Correl: Lucid: Please note the difference between dependent and non-dependent subuqeries
Pock: Keiter: so a derived table is a subquery used to build aggregates, and there are no other requirements?
Chudej: Keiter: can you give me an example of a subquery which does NOT build aggregates? isn’t that always the point?
Keiter: Lucid: you don’t need derived tables for those – just use joins
Demint: Oh like a subquery which builds a value?
Correl: Lucid: You don’t need subquery for aggregates :
Tugman: I understand derived != dependent and that dependent uses other values
Correl: Lucid: Didn you never hear about aggregate functions such as MAX, MIN, COUNT etc?
Keiter: Lucid: define “other values”, in this case.
Blanchett: Salle: I’m a programmer so this isn’t my specialty, but I want to improve
Zidzik: Keiter: values from the parent is that the right term? query
Keiter: Lucid: yes, the outer query. Good.
Berthelot: It has dependencies, pretty self-explanatory
Welz: I don’t really understand why all subqueries aren’t derived tables though
Correl: Lucid: You can for example use: SELECT sellerId, MAXrank, MAXdate FROM . GROUP BY sellerId; No subuqeries required in this case
Keiter: Lucid: I never ***ume anything, particularly when it comes to the understanding of sql users that join here.
Correl: Lucid: derived table = subquery in FROM
Decomo: Ok so I’m starting from a clean slate. This whole thing centers around the input the user provides
Correl: Lucid: Yes that’s better. Perhaps reading groupwise max article will help you too
Lamphere: When building a query like this is it okay for me to start from that point with the LIKE portion and then building from there?
Neuby: Not asking about the contents, just how you actually go about writing queries
Keiter: I try to avoid like and use full text search solutions instead
Keiter: Especially for commonly run queries
Keiter: Unless, of course, the tables are tiny, then it doesn’t matter.
Stecher: Keiter: I don’t think I’m really ready for that, this is a fairly static table of 50k rows, shouldn’t that be ok?
Keiter: Lucid: a full table scan of 50k rows can be expensive.
Dekorne: Keiter: last time fulltext search came up I bailed pretty quick due to the bar for entry
Shack: Keiter: most of the solutions just seemed unwieldy for the scope of me trying to improve my ability to write SQL queries
Keiter: Lucid: then just scan your 50k rows and be on your merry way
Mcnell: Keiter: this worse-case scenario on the Sellers table with my frankensetin query is 1.5s