Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Best practices for writing SQL queries

metabase.com

Post reply on HN
Print view View original

71–80 of 158 posts

Re: Best practices for writing SQL queries

#71

Post by richardeb »

tremon wrote: post #44

Avoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, p…

Advice would have to be tailored to specific database technologies and probably specific versions.

For example, in Apache Impala and Spark, "Prefer = to LIKE" is good advice, especially in join conditions, where an equijoin would allow the query planner to use a Hash Join, whereas a non equijoin limits the query planner to a Nested Loop join.

Re: Best practices for writing SQL queries

#72

Post by wolf550e »

fabian2k wrote: post #56
supernova87a wrote: post #42

Is there a good place to read from an advanced casual "lay user's" perspective what SQL query optimizers do in the background after you submit the query? I would love to know, so that I can know what optimizations and WHERE / JOIN conditions I should really be careful about making more efficient, versus others that I don't have to worry because the optimizer will take care of it. For example, if I'm joining 2 long ta…

My experience is with Postgres, this might vary for other databases. As already said, using EXPLAIN ANALYZE is very useful to see what the planner is doing. This might be hard to read for more complex queries, but it is quite understandable for simple ones. One of the more important parts is simply understanding which indexes can be used in a query. The other part is understanding when the database will intentionally…

This is a good explanation about join order optimization: https://www.sqlite.org/queryplanner-ng.html

No database can find perfect join order when you have more than about 8 to 10 tables in the join.

Re: Best practices for writing SQL queries

#73

Post by arh68 »

dspillett wrote: post #24
hn_throwaway_99 wrote: post #8

This is an aside, but a colleague years back showed me his preferred method formatting SQL statements, and I've always found it to be the best in terms of readability, I just wish there was more automated tool support for this format. The idea is to line up the first value from each clause. Visually it makes it extremely easy to "chunk" the statement by clause, e.g.: SELECT a.foo, b.bar, g.zed FROM alpha a JOIN beta…

I've taken to using a similar format too, though some seem to dislike it significantly. Other things I like for clarity and editing ease are prefix commas and lining up like parts, using something like your example: SELECT a.foo , b.bar , g.zed FROM alpha a JOIN beta b ON a.id = b.alpha_id AND a.another = b.thing LEFT JOIN gamma g ON b.id = g.beta_id WHERE a.val > 1 AND b.col or SELECT a.foo , b.bar , g.zed FROM alph…

I like your second version. My own style, still evolving, is to write more lines and align further left

  select
    a.foo
    , b.bar
    , g.zed
  from alpha a
  inner join beta b on
    b.alpha_id = a.id
    and b.thing = a.another
  left  join gamma g on
    g.beta_id = b.id
  where
    a.val > 1
    and b.col

Re: Best practices for writing SQL queries

#74

Post by musingsole »

richardeb wrote: post #71
tremon wrote: post #44

Avoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, p…

Advice would have to be tailored to specific database technologies and probably specific versions. For example, in Apache Impala and Spark, "Prefer = to LIKE" is good advice, especially in join conditions, where an equijoin would allow the query planner to use a Hash Join, whereas a non equijoin limits the query planner to a Nested Loop join.

This is ultimately my problem with databases. We use the term as a catchall, but every implementation is different and is unified only in that they store tables and can respond to SQL.

People treat deciding your app will have a database as a design decision when in reality it is only about 10% of a design decision.

Re: Best practices for writing SQL queries

#75

Post by shazzdeeds »

carbocation wrote: post #54
warent wrote: post #30

Earlier quoted context omitted.

this seems like taking on a pretty huge risk for a minor convenience. the difference between those two queries can mean the difference between protecting someone's PII

I'm not sure that I follow. The two queries are to demonstrate difference in form; they are not intended to be equivalent. If you're already writing: WHERE foo=bar AND biz=baz It's not clear to me how: WHERE TRUE AND foo=bar AND biz=baz is worse.

He’s saying if someone gets in the habit of using that style they have to be very careful. If they forget to change True to False when using an OR that it could have major consequences. Performance being the least of concerns.

Re: Best practices for writing SQL queries

#76

Post by adrianmonk »

tremon wrote: post #44

Avoid functions in WHERE clauses Avoid them on the column-side of expressions. This is called sargability [1], and refers to the ability of the query engine to limit the search to a specific index entry or data range. For example, WHERE SUBSTRING(field, 1, 1) = "A" will still cause a full table scan and the SUBSTRING function will be evaluated for every row, while WHERE field LIKE "A%" can use a partial index scan, p…

Also, some databases allow you to index the result of a function. Oracle calls them "function-based indexes". PostgreSQL seems to call them "indexes on expressions".

And MySQL seems to support "generated columns" which can be "virtual" and can have indexes. (Although in that case the expression lives in the column definition, so it's not actually in a where clause.)

Also, I guess some databases probably let you have an index on a view, which could be another way.

So if you really need a function in your where clause, there may very well be a way to do it efficiently. Of course, the usual caveat applies that it requires more I/O to maintain more indexes.

Re: Best practices for writing SQL queries

#77

Post by tomnipotent »

SigmundA wrote: post #20
Hjfrf wrote: post #18

Earlier quoted context omitted.

LIKE 'abc%' will use indexes but LIKE '%abc' will not. At least for the latest versions of every database. If you go back to a version from 10+ years ago there's no guarantees.

Pedantically if your database supports index scans it can use the index on the column to scan for '%abc' rather than the whole table which can be much faster while not as a fast as a seek. It can only do a seek if there are character before the wildcard: 'ab%c', 'abc%' and 'abc' getting progressively faster due to less index entries transversed.

> it can use the index on the column to scan for '%abc'

Using an index would just mean more overhead to fetch data later, so optimizers will prioritize a table scan in these cases since it would have less cost.

Re: Best practices for writing SQL queries

#78

Post by arrosenberg »

musingsole wrote: post #74
richardeb wrote: post #71

Earlier quoted context omitted.

Advice would have to be tailored to specific database technologies and probably specific versions. For example, in Apache Impala and Spark, "Prefer = to LIKE" is good advice, especially in join conditions, where an equijoin would allow the query planner to use a Hash Join, whereas a non equijoin limits the query planner to a Nested Loop join.

This is ultimately my problem with databases. We use the term as a catchall, but every implementation is different and is unified only in that they store tables and can respond to SQL. People treat deciding your app will have a database as a design decision when in reality it is only about 10% of a design decision.

That's a challenge with meatspace infrastructure too. You can have a standardized design for (e.g.) an airport, but "commercial jets" actually represents a wide variety of vehicles with different needs and tolerances, so all designs have to be adapted to the specific circumstances.

Re: Best practices for writing SQL queries

#79

Post by iblaine »

"Best practices for writing SQL queries in metabase" should be the title here.

10 or so years ago when SQL Server, Oracle & MySQL dominated the industry, you could talk about SQL optimization with the expectation that all advice was good advice. There are too many flavors of databases to do that today.

Re: Best practices for writing SQL queries

#80

Post by jolmg »

hn_throwaway_99 wrote: post #26
Hjfrf wrote: post #21

Earlier quoted context omitted.

Does that still look ok if you're selecting 10+ columns with functions, or would you split out the first line situationally?

Another commenter showed how this works: SELECT a.foo , b.bar , g.zed FROM ... While the comma placement may seem weird, it makes this exactly identical to the "AND" or "OR" placement in WHERE clauses, and the primary benefit is that it's easy to comment out any column except the first.

> While the comma placement may seem weird

It's not completely unconventional. Haskell is typically styled with that kind of comma usage, too. For example,

  [ 1
  , 2
  ]

  { foo = 1
  , bar = 2
  }
Coincidentally, SQL and Haskell are the only languages I know that use `--` for comments.
Post reply on HN