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

81–90 of 158 posts

Re: Best practices for writing SQL queries

#81

Post by deathanatos »

tomnipotent wrote: post #77
SigmundA wrote: post #20

Earlier quoted context omitted.

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.

I think it would depend, wouldn't it? If the query can be answered directly from an index (there exists some index containing all of the columns required by the query) then an index scan would suffice and be faster by virtue of not having to scan all the data (the index would be smaller by not including all columns). I believe most modern DB query optimizers are capable of this.

If there isn't such an index, then it's a toss up: yes, going to the main table to fetch a row has a cost, but if there are only a few rows answered by the query, then it might be worth it. If there are many rows, that indirection will probably outweigh the benefit of the index scan & we'd be better off with a table scan. This would require an optimizer to estimate the number of rows the query would find. I don't know if modern DB query optimizers would do this or not. (And my naïve guess would be "they don't", specifically, that the statistics kept are not sufficiently detailed to answer any generalized LIKE expression.)

Re: Best practices for writing SQL queries

#82

Post by SigmundA »

tomnipotent wrote: post #77
SigmundA wrote: post #20

Earlier quoted context omitted.

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.

Depends on estimated selectivity and if the index covers the result as well.

If the criteria would fetch few rows out of many it can be faster to scan the index then retrieve the few matching results and even better if the index covers the results it never touches the table itself (index only scan).

Re: Best practices for writing SQL queries

#83

Post by tomnipotent »

SigmundA wrote: post #82
tomnipotent wrote: post #77

Earlier quoted context omitted.

> 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.

Depends on estimated selectivity and if the index covers the result as well. If the criteria would fetch few rows out of many it can be faster to scan the index then retrieve the few matching results and even better if the index covers the results it never touches the table itself (index only scan).

> Depends on estimated selectivity

This can't be determined with LIKE suffix wildcards and that's not how any of the commonly-used index data structures work (b-tree, hash, gist, or bitmap). Index metadata will not help in eliminating leaf pages, and every row is going to need to be scanned.

Re: Best practices for writing SQL queries

#85

Post by tomnipotent »

deathanatos wrote: post #81
tomnipotent wrote: post #77

Earlier quoted context omitted.

> 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.

I think it would depend, wouldn't it? If the query can be answered directly from an index (there exists some index containing all of the columns required by the query) then an index scan would suffice and be faster by virtue of not having to scan all the data (the index would be smaller by not including all columns). I believe most modern DB query optimizers are capable of this. If there isn't such an index, then it'…

> I think it would depend

Not for LIKE clauses using suffix wildcards, unless you create an index specifically using such a condition (CREATE INDEX IX_blah ON table (column) WHERE column LIKE '%abc');

Re: Best practices for writing SQL queries

#86

Post by mulmen »

hobs wrote: post #23
eirki wrote: post #4

> Avoid SELECT title, last_name, first_name FROM books LEFT JOIN authors ON books.author_id = authors.id > Prefer SELECT b.title, a.last_name, a.first_name FROM books AS b LEFT JOIN authors AS a ON b.author_id = a.id Couldn't disagree more. One letter abbreviations hurt readability IMO.

Most of your queries shouldn't be so verbose as to confuse which aliases you are talking about, but I agree - here's the best format :) (because why put ON on another line anyway?) SELECT bo.title, au.last_name, au.first_name FROM books AS bo LEFT JOIN authors AS au ON bo.author_id = au.id

ON should be on another line because it gives context to bo.author_id = au.id.

Re: Best practices for writing SQL queries

#87

Post by saila »

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…

This is the style I've settled on lately, where all the major keywords are left-aligned and the clauses are consistently indented. It uses a bit more vertical space, but I find it easier to read than any other formatting style I've seen (in the wild or produced by formatters).

    select
      a.foo,
      b.bar,
      g.zed
    from
      alpha a
    join
      beta b
      on b.alpha_id = a.id
    left join
      gamma g
      on g.beta_id = b.id
    where
      a.val > 1
      and b.col
It's really easy, for me anyway, to get an overview of the query with this style compared to styles that are more cramped or that are inconsistently wrapped/indented.

For simpler queries, I think this is okay too, but only if the clauses easily fit on a single line:

    select ...
    from ...
    join ...
    where ...

Re: Best practices for writing SQL queries

#88

Post by [deleted] »

chrishas35 wrote: post #22
prepend wrote: post #13

Earlier quoted context omitted.

I prefer having some meaningful alias because trying to remember what a,b,c,d, etc gets annoying.

Author probably could have chosen a better example such that it doesn't look like the author chose letters sequently. In this case the letters are meaningful as they are the same as the first of the table name, a common convention, unfortunately that happens to be the first two letters of the alphabet...which yes, would be very annoying.

[deleted]

Re: Best practices for writing SQL queries

#89

Post by SigmundA »

tomnipotent wrote: post #83
SigmundA wrote: post #82

Earlier quoted context omitted.

Depends on estimated selectivity and if the index covers the result as well. If the criteria would fetch few rows out of many it can be faster to scan the index then retrieve the few matching results and even better if the index covers the results it never touches the table itself (index only scan).

> Depends on estimated selectivity This can't be determined with LIKE suffix wildcards and that's not how any of the commonly-used index data structures work (b-tree, hash, gist, or bitmap). Index metadata will not help in eliminating leaf pages, and every row is going to need to be scanned.

Yes every row of the index needs to be scanned not every row of the table which is faster than scanning the table.

I am most familiar with MS SQL server and it will most certainly do an index scan for what it thinks is a highly selective predicate with "suffix wildcards" and it can return results faster than scanning the table.

If the index covers the result columns it will scan the index and never touch the table otherwise it will do a key lookup to the table.

Re: Best practices for writing SQL queries

#90

Post by tehlike »

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…

Postgres and the likes allows trigram indexes for like queries, and expression based indexes just fine. This argument doesn't really pass the smell test.
Post reply on HN