#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

How to Optimize MySQL Queries: Practical Techniques

Most slow MySQL queries are slow for one reason: MySQL reads far more rows than the answer needs. Optimising a query means giving MySQL a way to read fewer rows (usually an index) and writing the query so it can use that way. This page takes one 50,000-row table and fixes five common problems in turn, checking each fix with EXPLAIN.

The sample table

Copy this into any MySQL 8 database. The recursive CTE generates 50,000 orders spread over 2024 and 2025 across five cities.

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  city        VARCHAR(30) NOT NULL,
  phone       VARCHAR(15) NOT NULL,
  order_date  DATE NOT NULL,
  amount      DECIMAL(10,2) NOT NULL
);

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO orders
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT i,
       1 + MOD(i * 7, 5000),
       ELT(1 + MOD(i, 5), 'Delhi','Mumbai','Pune','Jaipur','Indore'),
       CONCAT('98', LPAD(i, 8, '0')),
       DATE('2024-01-01') + INTERVAL MOD(i, 730) DAY,
       100 + MOD(i * 37, 4900)
FROM n;
ANALYZE TABLE orders;

In the EXPLAIN results below, watch three columns: type (ALL means a full table scan), key (the index used) and rows (MySQL's estimate of rows it will examine). The rows figures are estimates, so yours may differ slightly.

Problem 1: a function wrapped around the column

A December 2025 sales report is often written like this:

SELECT order_id, amount FROM orders
WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 12;

Even after CREATE INDEX idx_orders_date ON orders (order_date);, a WHERE YEAR(order_date) = 2025 filter still scans the table, because the index is sorted by the date, not by YEAR() of the date:

typekeyrowsExtra
ALLNULL50224Using where

Rewrite the condition as a range on the bare column:

EXPLAIN SELECT order_id, amount FROM orders
WHERE order_date >= '2025-12-01' AND order_date < '2026-01-01';
typekeyrowsExtra
rangeidx_orders_date2040Using index condition

2,040 rows examined instead of about 50,000, and a COUNT(*) confirms exactly 2,040 orders match. The half-open range (>= start, < next start) also avoids missing rows on the last day when the column is a DATETIME. MySQL 8.0.13 and later also support functional indexes such as CREATE INDEX idx_yr ON orders ((YEAR(order_date)));, but a plain range is simpler and works on every version.

Problem 2: the wrong column order in a composite index

Now filter by city as well and sort by date:

EXPLAIN SELECT order_id, order_date, amount FROM orders
WHERE city = 'Pune' AND order_date >= '2025-12-01'
ORDER BY order_date;

With only the date index, MySQL reads all 2,040 December rows and then throws away the 80% that are not Pune (filtered = 10.00, Using where). Add a composite index with the equality column first and the range column second:

CREATE INDEX idx_orders_city_date ON orders (city, order_date);
typekeyrowsExtra
rangeidx_orders_city_date408Using index condition

Now MySQL jumps straight to the Pune entries for December, already in date order, so there is no Using filesort either. An index on (order_date, city) would be much less useful, because once a range starts on the first column, the second column cannot narrow the search the same way.

Problem 3: fetching columns you do not need

Drop amount from the select list and the same index answers the whole query:

EXPLAIN SELECT order_id, order_date FROM orders
WHERE city = 'Pune' AND order_date >= '2025-12-01';
typekeyrowsExtra
rangeidx_orders_city_date408Using where; Using index

Using index means a covering index: every needed column is inside the index (InnoDB secondary indexes always carry the primary key, so order_id comes free). No table rows are read at all. This is why SELECT * in application code costs more than it looks.

Problem 4: comparing a string column with a number

The phone column is a VARCHAR with its own index. Compare the two queries:

CREATE INDEX idx_orders_phone ON orders (phone);
EXPLAIN SELECT order_id FROM orders WHERE phone = 9800012345;    -- number
EXPLAIN SELECT order_id FROM orders WHERE phone = '9800012345';  -- string
literaltyperowsExtra
numberindex50224Using where; Using index
stringref1Using index

With a numeric literal, MySQL has to convert every stored phone string to a number before comparing, so it walks the entire index. Quote the value (or bind it as a string parameter) and the lookup touches one row.

Problem 5: deep OFFSET pagination

LIMIT 10 OFFSET 40000 looks cheap but MySQL reads and discards 40,000 rows first (rows = 40010). Keyset pagination remembers the last ID shown and starts from there:

-- page after the one that ended at order_id 40000
SELECT order_id, amount FROM orders
WHERE order_id > 40000
ORDER BY order_id
LIMIT 10;

This is a primary-key range read that stops after 10 rows, however deep the page is.

A checklist before you blame the server

Two myths to drop: in MySQL 8, IN (subquery) and EXISTS are usually rewritten into the same semi-join plan, so swapping one for the other rarely helps by itself; and COUNT(*) is not slower than COUNT(1).

Next steps: learn to read every column of the plan in the EXPLAIN statement guide, see how B-tree indexes work in MySQL indexes explained, and find the queries worth fixing with the slow query log in how to find and fix slow queries. Server-level settings such as the buffer pool are covered in MySQL performance tuning tips.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →