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