Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

MySQL Index Usage

What Will You Learn in This Guide?​

  • Working logic of MySQL indexes
  • Analyzing query performance with EXPLAIN
  • Speeding up SELECT queries with single-column indexes
  • Ensuring data integrity with UNIQUE index
  • Effect of column order in multi-column indexes
  • List and delete existing indexes

Phase 1 – Content Analysis (Technical Summary)​

This guide covers the use and management of index in MySQL databases.
The aim is to improve query performance and prevent data duplication in large data sets.
Steps followed; sample database setup, query plan analysis with EXPLAIN, implementation of single-column, UNIQUE and multi-column indexes.

1. Preparation: Sample Database and Script​

A simple employee database is created for tests.

The following command creates and activates a new database:

CREATE DATABASE genixnode_index;
USE genixnode_index;
  • This command creates the table that stores employee information:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

2. Single Column Indexes and EXPLAIN Analysis​

  1. EXPLAIN is used to see how a query is executed by MySQL.
  • This command analyzes the execution plan of the query:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;
Eğer key alanı NULL ise, MySQL tüm tabloyu tarıyor demektir.
  • To improve performance, an index is added to the salary column:

CREATE INDEX salary_idx ON employees(salary);
  • After this operation the same query scans much fewer rows.

3. Data Security with Unique Index​

  1. In some fields, repeating values are not desired.
  • Device serial number is a typical example of this.

The following command creates a unique index:


CREATE UNIQUE INDEX serial_idx ON employees(device_serial);
  1. If a record is added with the same serial number after this point, MySQL will give an error.
  • In this way, data integrity is maintained.

4. Multi-Column Indexes​

  1. Multi-column indexes are used for queries with multiple conditions.
  • This command creates an index containing the surname and first name columns:

CREATE INDEX name_full_idx ON employees(last_name, first_name);

Important: MySQL uses this index from left to right.

  • If you search with last_name, the index works.
  • If you search only with first_name, the index is not used.

5. Managing Existing Indexes​

  1. To list all indexes in the table:

SHOW INDEXES FROM employees;
  • To delete an index that is no longer used:

DROP INDEX serial_idx ON employees;
  • This operation only removes the index, it does not affect the table.

Frequently Asked Questions (FAQ)​

1. Is it OK to add indexes to each column? No. Indexes increase reading speed but can slow down writing operations.

2. Do indexes use disk space? Yes. Indexes are additional data structures outside the table.

3. In what situation does MySQL not use indexes? If the query returns a large portion of the table, a table scan may be preferred.

4. How many indexes should a table have? Generally, 5–10 indexes are sufficient. Too much may reduce performance.

Result​

MySQL indexes are critical structures that directly affect query performance. With the right columns, the right index type and the right sorting, serious speed gains are achieved. Indexing strategy should be planned by balancing read and write performance.

You can establish scalable systems by applying this approach on the GenixNode infrastructure in projects that require high performance.