#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

MySQL Query Optimization Best Practices for Analysts

Analysts and developers write different kinds of SQL. A developer's query fetches one customer's orders in a millisecond; an analyst's query scans a quarter of sales, joins four tables and groups by city. For analysts, the slow query and the wrong query are often the same query: the join that makes it slow is also the join that double-counts revenue. These practices target both problems, and none of them needs DBA rights on the server.

Practice 1: aggregate before you join

The question: per customer, total order value and number of support tickets. Sample data:

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(15));
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE,
                     amount DECIMAL(10,2), KEY idx_date (order_date), KEY idx_cust (customer_id));
CREATE TABLE support_tickets (ticket_id INT PRIMARY KEY, customer_id INT, opened_on DATE);

INSERT INTO customers VALUES (1,'Mahesh','Kochi'), (2,'Nidhi','Kochi'), (3,'Omkar','Pune');
INSERT INTO orders VALUES (10,1,'2026-10-02',3000), (11,1,'2026-10-15',2000),
                          (12,2,'2026-10-20',4500), (13,3,'2026-10-21',1200);
INSERT INTO support_tickets VALUES (100,1,'2026-10-03'), (101,1,'2026-10-16'),
                                   (102,1,'2026-10-25'), (103,3,'2026-10-22');

The natural first attempt joins everything and then groups:

SELECT c.name, SUM(o.amount) AS revenue, COUNT(t.ticket_id) AS tickets
FROM customers c
LEFT JOIN orders o          ON o.customer_id = c.customer_id
LEFT JOIN support_tickets t ON t.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
namerevenuetickets
Mahesh15000.006
Nidhi4500.000
Omkar1200.001

Mahesh actually spent ₹5,000 and raised 3 tickets. His 2 orders were joined with his 3 tickets, producing 2 × 3 = 6 rows, so each order was counted three times and each ticket twice. This "fan-out" is the most common reason an analyst's total does not match finance's total. On large tables it is also slow, because MySQL builds and groups all those multiplied rows.

Aggregate each table to one row per customer first, then join:

WITH o AS (SELECT customer_id, SUM(amount) AS revenue FROM orders GROUP BY customer_id),
     t AS (SELECT customer_id, COUNT(*) AS tickets FROM support_tickets GROUP BY customer_id)
SELECT c.name, COALESCE(o.revenue, 0) AS revenue, COALESCE(t.tickets, 0) AS tickets
FROM customers c
LEFT JOIN o ON o.customer_id = c.customer_id
LEFT JOIN t ON t.customer_id = c.customer_id
ORDER BY c.customer_id;
namerevenuetickets
Mahesh5000.003
Nidhi4500.000
Omkar1200.001

Correct, and each join now matches at most one row. CTEs make this pattern readable; see common table expressions in MySQL.

Practice 2: do not "fix" duplicates with DISTINCT

Adding SELECT DISTINCT to the broken query above does not repair the sums, because the sums are computed before DISTINCT runs, and it adds a sort or temporary table on top. (In MySQL 8 it can even fail: combined with an ORDER BY on a column that is not selected, you get error 3065, "... is incompatible with DISTINCT".) If you see duplicate rows, find which join multiplies them instead.

Practice 3: filter dates as ranges

Monthly reports are often written with DATE_FORMAT(order_date, '%Y-%m') = '2026-10'. That hides the column inside a function, and EXPLAIN shows a full scan (type = ALL, no key) even though order_date is indexed. Write the month as a half-open range instead:

SELECT c.city,
       COUNT(DISTINCT o.customer_id) AS buyers,
       SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-10-01'
  AND o.order_date <  '2026-11-01'
GROUP BY c.city
ORDER BY revenue DESC;
citybuyersrevenue
Kochi29500.00
Pune11200.00

Now EXPLAIN shows type = range on idx_date. The same rule applies to YEAR(), MONTH(), DATE() and LOWER() on filtered columns. A full walk-through with timings is in how to optimize MySQL queries.

Practice 4: explore with LIMIT, aggregate with care

Practice 5: reuse results instead of recomputing them

If ten dashboard tiles each scan raw orders for the same month, compute the month once:

CREATE TABLE summary_city_month AS
SELECT DATE_FORMAT(o.order_date, '%Y-%m-01') AS month_start,
       c.city,
       COUNT(*)      AS orders,
       SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY month_start, c.city;

Dashboards then read a few hundred summary rows instead of millions of order rows. Refresh it nightly with a scheduled event, or for a one-off analysis session use a temporary table. For Power BI or Tableau, point the tool at a view or summary table rather than raw tables.

Practice 6: check the plan, then ask for an index

Analysts usually cannot create indexes on production, but they can run EXPLAIN and bring evidence. "This report filters on order_date and joins on customer_id; EXPLAIN shows a full scan of 4 crore rows" is a request a DBA can act on. Learn to read the output in the EXPLAIN statement guide.

Quick checklist before you share a number

For the bigger picture of how SQL fits an analyst's day, read MySQL for data analysis.

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 →