Hi team,
So I was using sample data from the employees table (from the s3 bucket singlestore-education/datasets/db_employees/) and was tuning the below query
SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date
FROM employees e
INNER JOIN dept_emp de ON e.emp_no=de.emp_no
INNER JOIN departments d ON de.dept_no=d.dept_no
INNER JOIN titles t ON e.emp_no=t.emp_no
ORDER BY e.first_name, e.last_name, d.dept_name, t.from_date
LIMIT 10;
When there was no shard keys, it joins the employees with the titles and has an execution time of 358 ms.
When I created another table titles_sharded from the titles table and added the emp_no column as a shard key. The join order changed and the query now takes 394 ms.
I was curious as to why the sharding key didn’t make a slight difference as this there would be less shuffling of data between the leaf nodes.
That data set is probably small. Adding a shard key could make it try to prefer a collocated join and that might change the join order. You probably also are measuring compile time and not only execution time. Small changes like that are not usually significant for real workloads. If you wanted to thoroughly optimize a query like this, you should get a profile or debug profile for both queries and make sure profile was run twice to exclude compile time for each. Then look at that profile in the profile GUI in the cloud portal and see where the time is getting spent. Then try to drive down the time by appropriate choice of table type (rowstore vs. columnstore) sorting, sharding, and indexes. Our docs have discussion of when to use these.
E.g. go to docs.singlestore.com and ask SqRL this “find documentation on how to choose sort and shard keys when designing your physical table schema”
Thank you for taking your time to answer my query. I did run the profile twice. The table does have data over 300K rows. Thank you for the SqRL tip, there was a lot of takeaways from the conversation. Adding a sort key on one side of the join eventually did cut the time by 1/4th. I was trying and testing query tuning across different databases to check the join order and I realized that plugging the table with shard keys provides the perfect join order.
Once again, thank you for your time.