Count of line item in MySQL

I have a complex query joined to multiple tables to show a “transaction history”, I want a quick and easy way to count the occurrences of order_id to make it as the line_number. I had it done with code but for performance reason I want to have it done with MySQL. Here is an example of what I have:

order_id    item_id  
---------   --------
1111111     444444   
1111111     555555   
1111111     666666   
1111112     123333   
1111112     121111   
1111113     123123   
1111114     123123   
1111115     123123   

This is what I want it to look like:

order_id    line_number    item_id      
---------  ------------    ---------    
1111111          1         444444   
1111111          2         555555   
1111111          3         666666   
1111112          1         123333   
1111112          2         121111   
1111113          1         123123   
1111114          1         123123   
1111115          1         123123   

I tried COUNT(order_id) but that didn’t work, unless there is a good way to do it. Thanks!

MySQL version 5.5.43

Answer

In MySQL 8.0, just use row_number():

select 
    order_id,
    row_number() over(partition by order_id order by item_id) line_number,
    item_id
from mytable
order by order_id, item_id 

In earlier versions, one solution (other than using variables) is to do an inline count with a subquery:

select
    order_id,
    (
        select 1 + count(*) 
        from mytable t1 
        where t1.order_id = t.order_id and t1.item_id < t.item_id
    ) line_number,
    item_id
from mytable t
order by order_id, item_id