I’m experiencing a performance issue with MySQL when deploying my application to a live server. The problem occurs when creating a short link for the first time after a period of inactivity. Specifically, the initial query to the database takes a long time to execute, but subsequent queries perform normally.

Context
I have a Node.js application using mysql2 with a connection pool. The application is deployed on Render, and the MySQL database is hosted on Clever Cloud. Here is how I’ve set up the connection and the controller function for creating short links:

Database Connection Configuration (config/db_connection.js):

const mysql = require('mysql2');

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10, // Adjust the limit based on your requirements
    queueLimit: 0
});

module.exports = pool.promise(); // Using promise-based pool for async/await
Controller Function for Creating Short Links (controllers/createLinkController.js):


const pool = require("../../config/db_connection");
const ShortUniqueId = require('short-unique-id');

const createLink = async (req, res, next) => {
let { original_url, userId, shortened_url } = req.body;

const uid = new ShortUniqueId();
const user_id = userId || process.env.DEFAULT_USER_ID;
const trackingId = uid.randomUUID(10);

const checkAndInsertLink = async () => {
    if (!shortened_url) {
        shortened_url = uid.randomUUID(6);
    }

    const insertQuery = `INSERT INTO links (user_id, original_url, shortened_url, tracking_id) VALUES (?, ?, ?, ?)`;
    try {
        await pool.query(insertQuery, [user_id, original_url, shortened_url, trackingId]);
        res.status(201).json({ shortened_url: `https://qct.netlify.app/${shortened_url}`, trackingId });
    } catch (error) {
        console.error('Error inserting link:', error);
        res.status(500).json({ error: 'Internal server error 1' });
    }
};

if (shortened_url) {
    const checkQuery = `SELECT * FROM links WHERE shortened_url = ?`;
    try {
        const [results] = await pool.query(checkQuery, [shortened_url]);
        if (results.length !== 0) {
            res.status(409).json({ message: "Link already exists" });
            } else {
                checkAndInsertLink();
            }
        } catch (error) {
            console.error('Error checking link:', error);
            res.status(500).json({ error: 'Internal Server error 2' });
        }
    }  else {
        checkAndInsertLink();
    }
};

module.exports = createLink;

Issue
When creating a short link for the first time after a long period of inactivity, the query to create the link is unusually slow. After the initial slow query, subsequent queries execute normally without delay.

Environment
Deployment Platform: Render
Database Host: Clever Cloud
Questions
Why might the initial query be slow after a period of inactivity?
Are there any specific settings or configurations I should check on Clever Cloud or Render that might affect performance?
What can be done to improve the performance of the initial query?
Any insights or suggestions would be greatly appreciated. Thank you!