Using SingleStore 7.3.7, I was benchmarking performance, and I joined two tables in different databases. The performance was great, considering that one of the tables had over 2 billion records. I tried moving the smaller table (100 million records) to the same database as the larger one. The query completed 10% faster. The difference was consistent and noticeable.
Is there better performance joining tables from the same database? or did I experience an anomaly?
The most substantial difference you should notice when you do a cross-database join is if you are using collocated joins, where both tables are in the same DB and are sharded on their respective join columns.
Collocated joins are not possible across databases because each database has its own, independent partitioning strategy.
You might notice some other differences related to parallelism, say, if one database has more partitions than the other, independent of collocated joins.
Can you shared your benchmark info with us ? sys spec and timing