Introduction

Getting a row number in MySQL depends on which version you are using and what kind of numbering you actually need. In modern MySQL, the clean solution is the ROW_NUMBER() window function. In older MySQL versions, developers often used session variables as a workaround, but that approach is less robust and should be treated as a legacy technique.

Use ROW_NUMBER() in Modern MySQL

If you are on MySQL 8.0 or newer, use the window function directly.

1SELECT
2    ROW_NUMBER() OVER (ORDER BY age) AS row_num,
3    name,
4    age
5FROM people;

This assigns row numbers according to the ORDER BY inside the window definition. That ordering is crucial, because row numbers without a defined order are not meaningful.

If you want row numbers within groups, add PARTITION BY.

1SELECT
2    department,
3    name,
4    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
5FROM employees;

That restarts numbering inside each department.

Understand What the Number Means

A row number is not a stored property of the table. It is a value computed for the result set you are asking for.

That means if the ordering changes, the row number changes too. This is why ROW_NUMBER() is often used for:

• ranked reports
• paging and slicing
• top-n-per-group queries
• deterministic presentation order

It should not be confused with a persistent ID column.

Legacy MySQL Used Session Variables

Before window functions were available, a common workaround used a session variable.

1SET @row_number := 0;
2
3SELECT
4    (@row_number := @row_number + 1) AS row_num,
5    name,
6    age
7FROM people
8ORDER BY age;

This often works in simple cases, but it is more fragile than the window-function approach. It depends on execution details and is best treated as a compatibility technique for old systems rather than as the preferred modern pattern.

If you are maintaining an older codebase, it may still be the only option, but new code should use ROW_NUMBER() where possible.

Use Row Numbers for Pagination Carefully

A row number can help build result slices.

1WITH numbered AS (
2    SELECT
3        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num,
4        id,
5        title
6    FROM articles
7)
8SELECT *
9FROM numbered
10WHERE row_num BETWEEN 11 AND 20;

This is easy to read, but it is not always the fastest pagination strategy for very large datasets. For high-volume APIs, keyset pagination is often a better design than deep offset-style numbering.

So the right question is not only "can I number the rows" but also "is numbered pagination the query pattern I really want?"

Ordering and Indexing Still Matter

ROW_NUMBER() does not remove the cost of sorting. If you number rows by a column with poor indexing or huge cardinality, the query may still be expensive.

That is why window functions solve the expression problem more than the performance problem. The database still needs to evaluate the requested order.

Common Pitfalls

The most common mistake is asking for row numbers without defining a meaningful order. Another is treating the row number as if it were a stable table identity instead of a value derived from the query result.

Developers maintaining older MySQL versions also often rely on session-variable numbering without realizing it is a workaround rather than a first-class feature.

Finally, row-number pagination can be convenient but not always the best scaling strategy. If the result set is large and the query is latency-sensitive, revisit the access pattern, not just the SQL syntax.

Summary

• In MySQL 8.0 and newer, use ROW_NUMBER() for row numbering.
• Always define the numbering order explicitly.
• Use PARTITION BY when numbering should restart within groups.
• Session variables are a legacy workaround for older MySQL versions.
• Row numbers are computed result-set values, not permanent row identities.