CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-cloud-spannercockroachdbtidb
Kumar Siva
Kumar Siva

Reputation: 349

How do joins work in the Cloud Spanner database?

How do joins work in Google's Cloud Spanner database?

Will the join query execute fast? Is there any comparison between SQL(MySql, Oracle, MSSQL) vs NewSQL(Cloud Spanner/TiDB/CockroachDB)?

Upvotes: 3

Views: 4234

Answers (3)

Li Shen
Li Shen

Reputation: 313

TiDB supports join. TiDB stores data in tikv-server and handle sql in tidb-server. tidb-server will fetch data from tikv-server and process the sql logic.

By default, TiDB use parallel HashJoin operator as physical join operator. You can also choose SortMergeJoin and IndexLookupJoin operator by adding hint in sql statement. For most cases, HashJoin is a good choice. But in some scenarios, SortMergeJoin or IndexLookupJoin will be better. And the last two operators are more memory friendly. In the next release, TiDB will choose which physical join operator to use according to cost.

The following picture shows how parallel HashJoin operator works in TiDB. Parallel HashJoin

Upvotes: 3

Alex Robinson
Alex Robinson

Reputation: 13377

It's unlikely to be exactly the same as in Spanner, but if you're curious to learn more detail about how joins work and get optimized in a NewSQL database I'd recommend checking out CockroachDB's blog posts on joins:

https://www.cockroachlabs.com/blog/cockroachdbs-first-join/ https://www.cockroachlabs.com/blog/better-sql-joins-in-cockroachdb/

Upvotes: 1

Albert Cui
Albert Cui

Reputation: 394

Cloud Spanner supports inner, cross, full, left, and right joins. Joins are fast, but since Cloud Spanner distributes data across many splits, any time you can restrict a query to fewer splits, the faster the query will be. Cloud Spanner supports interleaving of your data to define parent-child relationships which allows you to effectively prejoin your data for quicker queries.

Upvotes: 3

Related Questions