A Complete Guide to MySQL Indexing

MySQL is a powerful relational database management system, and one of its most important features is indexing. Indexing significantly speeds up database queries by reducing the amount of data MySQL needs to scan. In this article, we’ll walk you through setting up MySQL indexes and demonstrate their impact on query performance using a simple Node.js application.

What is MySQL Indexing?

Indexing in MySQL is similar to the index in a book. It helps you quickly locate data without having to scan every row in a table. By creating an index on a column (or set of columns), MySQL can directly access rows that meet the search criteria, drastically improving query performance, especially with large datasets. In this guide, we’ll set up a MySQL database, create an index, and demonstrate how indexing improves query performance through a Node.js application.

Set Up MySQL Database and Table

Let’s start by creating a MySQL database and a users table to work with. We’ll insert some sample data into the table and then create an index on the name column for better performance.

SQL Script for Database Setup

Here’s the SQL script that creates the database, a users table, inserts some sample data, and adds an index to the name column.

-- Create the database
CREATE DATABASE IF NOT EXISTS mydb;

-- Use the database
USE mydb;

-- Create the users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Tom Brown', '[email protected]'),
('Alice Johnson', '[email protected]'),
('Bob White', '[email protected]');

-- Create an index on the 'name' column for faster lookup
CREATE INDEX IF NOT EXISTS idx_name ON users(name);

This script:

Creates the database `mydb` (if it doesn’t exist already).

Creates a users table with `id`, `name`, `email`, and `created_at` columns.

Inserts some sample user data.

Creates an index on the `name` column to speed up searches based on `name`.

Running the SQL Script

To execute the SQL script, save it to a file called setup_mysql.sql, and then run it on your MySQL server:

1	mysql -u root -p < setup_mysql.sql

This will create the mydb database, the users table, insert sample data, and create the index on the name column.

Setting Up the Node.js Application

Next, we’ll set up a simple Node.js application that interacts with the MySQL database. This application will have two routes:

One that forces MySQL to do a full table scan (no index used).

One that uses the index on the name column for optimized querying.

Install Dependencies

First, create a directory for your project and initialize the Node.js application:

mkdir mysql-indexing-demo
cd mysql-indexing-demo
npm init -y
npm install mysql2 express

This installs the mysql2 library to connect to MySQL and express for the web server.

Create the index.js File

Next, create a file named index.js in the project folder and add the following code:

const express = require('express');
const mysql = require('mysql2');

const app = express();
const port = 3000;

// MySQL connection pool
const pool = mysql.createPool({
  host: 'localhost',    // Change this to your MySQL server IP
  user: 'root',         // MySQL user
  password: 'password', // MySQL password
  database: 'mydb',     // Database name
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Fetch user by name (force no indexing, using full table scan)
app.get('/user-no-index/:name', (req, res) => {
  const { name } = req.params;
  console.time('query-time-no-index');  // Start time tracking
  pool.query('SELECT * FROM users FORCE INDEX (PRIMARY) WHERE name = ?', [name], (err, results) => {
    console.timeEnd('query-time-no-index');  // Log the query time
    if (err) {
      res.status(500).send(err);
    } else {
      res.json(results);
    }
  });
});

// Fetch user by name (using index for optimized query)
app.get('/user-with-index/:name', (req, res) => {
  const { name } = req.params;
  console.time('query-time-with-index');  // Start time tracking
  pool.query('SELECT * FROM users WHERE name = ?', [name], (err, results) => {
    console.timeEnd('query-time-with-index');  // Log the query time
    if (err) {
      res.status(500).send(err);
    } else {
      res.json(results);
    }
  });
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

This code sets up an Express server with two routes:

`/user-no-index/:name`: Fetches a user by name without using the index (forces a full table scan).

`/user-with-index/:name`: Fetches a user by name using the index on the `name` column.

Running the Node.js Application

Now, run the Node.js application:

1	node index.js

This will start the Express server on port 3000.

Testing the Performance

To test the performance differences:

Open your browser or use curl to test the routes.

Non-Indexed Route: Visit `http://localhost:3000/user-no-index/John` Doe. This will use a full table scan and take longer.

Indexed Route: Visit `http://localhost:3000/user-with-index/John` Doe. This will use the index and be much faster.

You will notice a performance difference in the console logs where the query time for the non-indexed query will be longer.

Conclusion

In this tutorial, we demonstrated the importance of indexing in MySQL and how it can drastically improve the performance of queries. We:

Set up a MySQL database and created a table with an index on the name column.

Built a simple Node.js application to query the database using both indexed and non-indexed queries.

Tested the performance difference between the two query methods.

By using indexing, MySQL can quickly locate the data it needs without scanning the entire table, significantly improving query response times, especially for large datasets. Indexing is an essential technique for optimizing database performance, and understanding how to use it effectively is a critical skill for any developer working with databases.You can find the all the related commands for this tutorial from here.