php - Choose an index for mysql table -

the table

i got table contains price 1 000 000 articles. articles got uniques id-number table contains prices multiple stores. if 2 stores got same article uniques id not unique table.

table structure

table articles

id int

price in

store varchar(40)

daily use

except queries using id-number users need run daily updates data csv-files insert/update each article in table. choosen procedure try select article , perform either insert or update.

question

with in mind, key should choose?

here solutions ive been considering:

• fulltext index of fields isbn , store
• add field value generated by isbn , store set primary key
• one table per store , use isbn primary key

use compound primary key consisting of store id , article id - that'll give unique primary key each item on per-store basis , don't need separate field (assuming store id , article id in table).

ideally should have 3 tables... like:

article -------------------------------------------- id | isbn | ... etc ...   store -------------------------------------------- id | description | ... etc ...   pricelist -------------------------------------------- article_id | store_id | price | ... etc ... 

with primary key pricelist being compound key made of article_id , store_id.

edit : (updated incorporate answer comment)

even on million rows update should ok (for definition of ok, might still take little while 1 million+ rows) since article_id , store_id comprise primary key - they'll both indexed.

you'll need write query it's along lines of:

update pricelist set price = {$fnewprice}  article_id = {$iarticleid}  , store_id =` '{$sstoreid}' 

though may want consider converting primary key in store table (store.id - , therefore pricelist.store_id in pricelist table) either unsigned int or char(30).

whilst varchar more efficient when comes disk space has couple of drawbacks:

1: mysql isn't keen on updating varchar values , can make indexes bloat bit may need run optimize table on (i've found on order_header table before).

2: (myisam) table non-fixed length fields (such varchar) have have dynamic row format less efficient when comes querying - there's more information on post: mysql row format: difference between fixed , dynamic?