Can SQL handle large datasets? Indexes and query design in MySQL

Many people see one slow query and immediately conclude that SQL is not suitable for large data. That conclusion is usually wrong.

In practice, the real problem is often:

• the schema is weak
• the right indexes are missing
• the query shape forces the database to scan too many rows
• joins happen too early before the dataset is reduced
• filtering, sorting, or pagination are pushed into application code instead of SQL

A system with tens or even hundreds of millions of rows can still perform well if the indexes and query design are correct.

Quick conclusion

If you only remember five points, keep these:

1. SQL is not weak at scale; the real problem is usually full table scans and bad query shape
2. indexes decide whether MySQL can jump to the right slice of data or scan the whole table
3. for large queries, filter early, limit early, then join or aggregate
4. e-commerce workloads in the shape of Shopify-like systems can still run well on MySQL for customers, orders, and order items when the schema and indexes are sound
5. SQL optimization is not "add indexes everywhere"; it is understanding which columns drive WHERE, JOIN, ORDER BY, and GROUP BY

The biggest mistake: assuming SQL must read everything before filtering

This is one of the most common misunderstandings.

Many people imagine the database doing this:

1. read the whole table
2. join every related table
3. filter at the very end

If that were true, any query on hundreds of millions of rows would collapse.

But that is not how a SQL optimizer is meant to work. Its goal is to:

• choose the right index
• reduce the number of rows read as early as possible
• reduce the number of rows that need to be joined
• reduce the amount of sorting work

So when someone says "SQL cannot handle large datasets", the right follow-up questions are:

• is the query using the right index?
• is the filter selective enough?
• does the join happen only after the dataset is reduced?
• is the query fetching unnecessary rows or columns?

Why indexes are the first thing you need to understand

An index helps MySQL avoid scanning the entire table just to find the rows you need.

Imagine an orders table with 200 million rows:

SELECT id, user_id, total_amount, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-06-01'
  AND created_at < '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

Without the right index, MySQL may:

• scan a huge number of rows
• filter for status = 'paid'
• sort the remaining rows by created_at
• only then return 50 rows

With a better composite index:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

the database has a chance to:

• jump straight into the status = 'paid' slice
• read only the needed time range
• work with a much smaller row set

The important detail is that an index is not only about WHERE. It can also reduce the cost of ORDER BY and LIMIT when the query shape matches.

The dangerous number is not table size, but scanned rows

A table with 300 million rows is less scary than a query that scans 300 million rows on every request.

The key questions are not only:

• how large is the table?

They are:

• how many rows does this query read?
• how many rows remain after filtering?
• how many rows are joined?
• how many rows are sorted?

For example:

A poor query

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-06-18';

Problems:

• DATE(created_at) can make it harder for MySQL to use an index on created_at
• SELECT * fetches too many columns
• there is no row limit

A better version

SELECT id, user_id, total_amount, created_at
FROM orders
WHERE created_at >= '2026-06-18 00:00:00'
  AND created_at < '2026-06-19 00:00:00';

This version is better because:

• it keeps the raw created_at column usable for indexing
• it only selects the columns the application actually needs
• it reduces I/O between MySQL and the application

The common mistake when joining large datasets

A very common mistake is joining too early while the base table is still too large.

Example:

SELECT o.id, o.created_at, c.name, c.email, SUM(oi.quantity * oi.price) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

At scale, this can be expensive because:

• orders has not been reduced before the join
• order_items is usually very large
• sorting and aggregation happen on a much wider dataset than needed

A better way to think is:

1. reduce the base table first
2. keep only the IDs you need
3. then join the other tables

Example:

SELECT o.id, o.created_at, c.name, c.email, SUM(oi.quantity * oi.price) AS total
FROM (
  SELECT id, customer_id, created_at
  FROM orders
  WHERE status = 'paid'
    AND created_at >= '2026-01-01'
  ORDER BY created_at DESC
  LIMIT 100
) o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.created_at, c.name, c.email
ORDER BY o.created_at DESC;

The point is not "subqueries are fancy". The point is:

• reduce rows before joining
• keep aggregation smaller
• avoid joining millions of orders just to return 100 rows

Why composite index order matters in real queries

Many teams have indexes and still get poor performance because the index does not match the query pattern.

Example query:

SELECT id, customer_id, created_at
FROM orders
WHERE store_id = 25
  AND status = 'paid'
  AND created_at >= '2026-06-01'
  AND created_at < '2026-07-01'
ORDER BY created_at DESC
LIMIT 100;

A more useful index is often:

CREATE INDEX idx_orders_store_status_created_at
ON orders (store_id, status, created_at);

Why:

• store_id and status narrow the dataset first
• created_at is the range condition and also supports sorting

By contrast:

CREATE INDEX idx_orders_created_status_store
ON orders (created_at, status, store_id);

may be less useful for this query because the column order does not follow the real access pattern.

That is the detail many teams miss:
an index needs the right columns and the right order.

SQL is strongest when large filtering happens inside the database

A wasteful pattern is pulling tens of thousands of rows into the backend and then processing them there.

Example:

SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE created_at >= '2026-06-01';

Then in application code:

const rows = orders
  .filter((item) => item.status === "paid")
  .sort((a, b) => b.createdAt.localeCompare(a.createdAt))
  .slice(0, 50);

This is slower in three places:

• MySQL returns too many rows
• the network between DB and app carries unnecessary data
• the application wastes RAM and CPU on work SQL is better at

A better query is:

SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE created_at >= '2026-06-01'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

At tens of millions of rows, that difference becomes very significant.

A Shopify-shaped example with millions of customers

Imagine an e-commerce platform with:

• customers: 5 million customers
• orders: 120 million orders
• order_items: 600 million order item rows

This is a realistic shape for a Shopify-like workload or for a mature store with years of data.

The wrong approach is a query like:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

Without strong indexing, this becomes heavy because:

• it reads a huge order dataset
• it joins customers too early
• it groups over a large working set

A better direction is:

1. define the business question clearly
2. filter the time range first
3. aggregate only on the filtered slice
4. join other large tables only when they are needed for display

Example:

SELECT c.id, c.name, x.order_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  WHERE created_at >= '2026-01-01'
    AND created_at < '2027-01-01'
    AND status = 'paid'
  GROUP BY customer_id
  ORDER BY order_count DESC
  LIMIT 100
) x
JOIN customers c ON c.id = x.customer_id
ORDER BY x.order_count DESC;

The thinking here is:

• aggregate inside orders first
• keep only the top 100 customers
• then join to customers

With an index such as:

CREATE INDEX idx_orders_status_created_customer
ON orders (status, created_at, customer_id);

the optimizer has a much better chance than if everything is joined up front.

Why can MySQL still be slow even when indexes exist?

Common reasons:

• the index column order is wrong
• a function is used on the filtered column
• LIKE '%keyword%' is used on a BTREE index
• joins happen on columns without suitable indexes
• the query returns too many columns or rows
• data distribution misleads the optimizer into a bad plan

So the right conclusion is usually not:

• we added indexes and it is still slow, so MySQL is weak

It is more often:

• indexes exist, but they do not match the real query pattern

What should you check before blaming SQL?

At minimum, check:

1. EXPLAIN or EXPLAIN ANALYZE
2. how many rows the query scans
3. which index the query uses
4. whether you see Using filesort, temporary, or ALL
5. whether the query still uses SELECT *
6. whether large joins happen before filtering

Example:

EXPLAIN
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-06-01'
ORDER BY created_at DESC
LIMIT 50;

If you see:

• very high scanned row counts
• type: ALL
• key: NULL

then the next step is usually not "switch databases". It is to fix the query shape and indexing first.

A short checklist for tables with tens of millions of rows

1. Does the query filter early enough?
2. Do the WHERE, JOIN, and ORDER BY columns have suitable indexes?
3. Is the composite index column order aligned with the query?
4. Is any function breaking index usability?
5. Are you still using SELECT *?
6. Can you LIMIT or aggregate before joining?
7. Are filtering, sorting, or pagination happening in code instead of SQL?

Conclusion

SQL is not weak at large scale. What usually slows systems down is forcing the database to read too much unnecessary data.

If you understand the role of indexes, reduce data before joins, and shape queries around the real business need, MySQL can still handle workloads with tens or even hundreds of millions of rows.

Before saying "SQL is not suitable for large datasets", check these three things first:

1. are the indexes correct?
2. is the query shape correct?
3. is the database scanning too many unnecessary rows?