multiple columns - multi-to-multi indexing issue, mysql -

i have multi-to-multi table, going have millions of rows. let me describe confusion example.

example:

table: car_dealer_rel

opt:1

columns: car_id: int unsigned, dealer_id: int unsigned index on: car_id, dealer_id 

car_id|dealer_id -------|--------- 1      | 1 1      | 2 .... 

• sub-opt:1: here can have 1 index on both columns.
• sub-opt:2: 1 combined index on 2 columns.

---

opt-2:

one column table

col: car_id_dealer_id: varchar:21 index on: pki on single column. 

here idea put values as: car_id.dealer_id , searches %.xxx , or xxx.%

car_id_dealer_id ---------------- 1.1 1.2 1.15 2.10 ... ... 

after millions of records faster for:

1. read from
2. add/update/delete.

i novice on mysql, appreciated.

with first 1

car_id|dealer_id -------|--------- 1      | 1 1      | 2 

you can easlily create composite index fo both sides

create index  ind1 on  car_dealer_rel (car_id,dealer_id );   create index  ind2 on  car_dealer_rel (dealer_id, car_id );  

that work fast

and can filter in both sense

 car_id =  your_value   

or

where dealer_id = another_value  

or using both

with second 1 can't easily( need string manipulation , don't let use index) , in condition can't using sql

and update, insert , delete performance remain pratically same