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

How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

youtube.com

Post reply on HN
Print view View original

51–60 of 134 posts

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#51

Post by dicroce »

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts.

In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what if there was a different query language with explicit index syntax.. I think you'd get a lot more predictable performance.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#53

Post by wgjordan »

dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

> what if there was a different query language with explicit index syntax..

There is, it's a feature in MySQL called Index Hints [1].

[1] https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#54

Post by dang »

codetrotter wrote: post #30
ryanworl wrote: post #3

There is a more recent lecture on the same topic from 2015 at CMU: https://youtu.be/gpxnbly9bz4

Mods please update link to this and change title to reflect the year of the replacement video

Does it cover the same material though? I don't want to deprive people of the original survey.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#55

Post by brianwawok »

wgjordan wrote: post #53
dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

> what if there was a different query language with explicit index syntax.. There is, it's a feature in MySQL called Index Hints [1]. [1] https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

Or oracle has it

A DBA can even sit there as queries fly past, and add hints on the fly.

And then you change a query from "select" (lowercase) to "SELECT" (uppercase), and query plans break and you break production.

Fun times

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#56

Post by squirrelicus »

aardvark291 wrote: post #33
blattimwind wrote: post #13

Earlier quoted context omitted.

You're not scared, you're just too lazy to learn the tools of your trade. Databases are not very complex and use pretty much only textbook data structures and algorithms. Understanding how they process a given query and how a query will probably perform/scale (even without EXPLAIN ANALYZE) is not hard to learn. You do need to learn it (at some point; you don't for small data, which is most). But it's far from difficu…

>Databases are not very complex and use pretty much only textbook data structures and algorithms skeptical expression

Whether they use highly tuned or proprietary or obscure algorithms (they do) is less important than the fact that understanding b-trees and basic normal forms will get you 90% of the way to understanding how to use one. It's just not that hard as a database user.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#58

Post by dragonwriter »

jokoon wrote: post #6

I don't like to use SQL engine because I don't understand how they work, I never really know if my query will be O(1), O(log(n)), O(n), etc, or what kind of algorithm will optimize my query. Who really does understand how a SQL engine work? Don't you usually require to understand how something work before starting using it? Which SQL analyst or DB architect really knows about the internals of a SQL engine? Do they kn…

> Who really does understand how a SQL engine work?

Presumably, at a minimum, all the people who work on such engines, including committees to the various open-source ones.

But also lots of other people.

> Don't you usually require to understand how something work before starting using it?

No. Very few programmers understand how compilers work before they start using them. I'd say it's more common to require working on something to really understand how it works than the reverse.

> I think SQL was designed when RAM was scarce and expensive, so to speed up data access, it has to be properly indexed with a database engine. I really wonder who, today, have data that cannot fit in RAM, apart from big actors.

Indexing is no less important for in-memory data access.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#59

Post by DenisM »

dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

Two reasons to not have indexes in the query:

1. Query expresses the result it produces, not the method that was used to obtain it. Semantic vs implementation. It may be a pain to write, but it will be easier to read later.

2. DBA could add/drop indexes on the fly to tune performance of a live system without making any changes to the application code. And being 100% certain he is not changing the semantics of what's going on.

As others noted, if you must you can use query hints for force particular index to be used for a particular operation. MSSQL also allows to pin down a query plan you like for a given query so that it doesn't drift away later due to environment changes.

I agree it is sometimes a pain to force SQL to use the index you wanted it to use.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#60

Post by ryanworl »

dang wrote: post #54
codetrotter wrote: post #30

Earlier quoted context omitted.

Mods please update link to this and change title to reflect the year of the replacement video

Does it cover the same material though? I don't want to deprive people of the original survey.

I don’t think it is worth changing the link. They are not the exact same material and anyone interested in SQLite should just watch both.
Post reply on HN