I think the only big improvement that can be made is to check which shard to query based on theuserIds of the followed users. One easy way is to check the last number of eachuserId (0,1,2,3,4,5,6,7,8,9) and then find which shard I need to query if I have 10.
Besides that is there anything wrong?
I am wondering if it makes sense to call the shards in afor loop, or there's a way to call it once and let it get handled by a library.
const express = require('express');const mysql = require('mysql2/promise');const app = express();const port = 3000;// Replace with your actual database connection detailsconst shardConfigs = [ { host: 'shard1_host', user: 'shard1_user', password: 'shard1_password', database: 'shard1_database', }, { host: 'shard2_host', user: 'shard2_user', password: 'shard2_password', database: 'shard2_database', }, // Add configurations for other shards as needed];// Create connections for each shardconst shardConnections = shardConfigs.map(config => mysql.createConnection(config));app.get('/user/feed/:userId', async (req, res) => { try { const userId = req.params.userId; // Step 1: Identify Sharded Database Shard (Hash-based Strategy) const selectedShardIndex = userId % shardConfigs.length; const selectedShardConnection = shardConnections[selectedShardIndex]; // Step 2: Query User's Followings const followingQuery = 'SELECT followed_user_id FROM followers WHERE follower_user_id = ?'; const [followingRows] = await selectedShardConnection.execute(followingQuery, [userId]); const followingUsers = followingRows.map(row => row.followed_user_id); // Step 3: Retrieve Tweets from Followed Users const tweets = []; for (const shardConnection of shardConnections) { const tweetsQuery = 'SELECT * FROM tweets WHERE user_id IN (?) ORDER BY timestamp DESC'; const [shardTweets] = await shardConnection.execute(tweetsQuery, [followingUsers]); tweets.push(...shardTweets); } // Step 4: Sort and Combine Tweets const combinedTweets = tweets.sort((a, b) => b.timestamp - a.timestamp); // Step 5: Limit and Paginate // Add logic here to limit the number of tweets returned, implement pagination if needed // Step 6: Return the Feed to the User Interface const feedData = combinedTweets.map(tweet => ({ tweet_id: tweet.tweet_id, user_id: tweet.user_id, content: tweet.content, })); res.json({ feed: feedData }); } catch (error) { console.error(error); res.status(500).json({ error: 'Internal Server Error' }); }});app.listen(port, () => { console.log(`Server is running at http://localhost:${port}`);});
1 Answer1
You're usingmysql.createConnection(config) inside amap function which is synchronous whilemysql.createConnection is asynchronous and returns a Promise thus you might end up with aPromise object instead of a connection object inshardConnections. You should await these connections inside an async function before starting your server.
async function initShardConnections() { for (let i = 0; i < shardConfigs.length; i++) { shardConnections[i] = await mysql.createConnection(shardConfigs[i]); }}// Call this function before starting your serverinitShardConnections().then(() => { app.listen(port, () => { console.log(`Server is running at http://localhost:${port}`); });});Querying each shard in a loop is not the best way, as it does not utilize the potential for parallel queries. A better approach is to initiate all shard queries simultaneously usingPromise.all and then combine the results thus your queries will run in parallel, reducing the overall response time.
const tweetPromises = shardConnections.map(shardConnection => shardConnection.execute(tweetsQuery, [followingUsers]));const results = await Promise.all(tweetPromises);const tweets = results.flatMap(result => result[0]);
