MySQL Performance Issue: Initial Query Slow After Period of Inactivity but Fast on Subsequent Queries

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;

My database structure:

CREATE TABLE links (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
original_url VARCHAR(2048) NOT NULL,
shortened_url VARCHAR(100) NOT NULL UNIQUE,
tracking_id VARCHAR(255) NOT NULL,
clicks INT DEFAULT 0,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES userList(id)
);
ALTER TABLE links
ADD INDEX idx_tracking_id (tracking_id);
CREATE TABLE link_tracking (
id INT PRIMARY KEY AUTO_INCREMENT,
tracking_id VARCHAR(100),
ip_address VARCHAR(100),
country VARCHAR(100),
city VARCHAR(100),
user_agent VARCHAR(2000),
browser VARCHAR(100),
os VARCHAR(100),
battery_status VARCHAR(100),
opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (tracking_id) REFERENCES links(tracking_id)
);

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!