database design programatically handling incrementing ID field, scalability - confused

Question

Hey,

If I am building a multi-shop e-commerce solution and want the orders table to maintain a shop based sequential ID, what is the best way of doing this?

For instance imagine these order IDs in sequence: -

UK0001
UK0002
UK0003
DE0001
UK0004
DE0002

etc.

1. through grouped PK ID MySQL / MyISAM - MySQL will manage this automatically if a country field and an auto incrementing ID field are used. But MyISAM has some inherent problems such as table locking and this feature seems like it's feature that is only available in MyISAM so moving database engine would not be possible with this solution.

2. Programmatically. Let's say we have two fields: order_id (global auto inc PK column managed by DB), order_number (country specific sequential ID field maintained through code) and the table also has a shop_id column to associate orders to shops.

So - after the new order record has been created and the DB engine has assigned an ID to the new record, and the newly created order ID has been retrieved in code as variable $newID

select order_number+1 as new_order_number from orders where order_id < $newID and shop_id = UK order by order_id desc limit 1

(this is pseudo code / sql btw)

Questions: 1. is this a feasible solution? Or is there a better more efficient way to do this?

1. When the table has 1 million + records in it, will the additional query overhead per order submission cause problems, or not?

2. It seems there'd be a chance of order_number clashes if two orders are placed for the same country and they get processed simultaneously. If this a possibility? if so, is there a way of protecting against it? (perhaps a unique index and a transaction?)

Look forward to your help!

Thanks