If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:
One-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null,
Name varchar(100) not null,
ProducerId int not null foreign key references Producer(id)
)
go
Many-To-Many
create table Producer
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table Product
(
id int identity(1, 1) not null primary key clustered,
Name varchar(100) not null
)
go
create table ProductProducer
(
ProductId int not null foreign key references Product(id),
ProducerId int not null foreign key references Producer(id)
)
go
-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer
primary key (ProductId, ProducerId)
go
If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.
Yes, in order to use the date column efficiently you need the date first in the index - or as you suggest, by itself. If you can sacrifice auto increment, you can change the index order to (date,id), but then you'd need another index for id for the 5% queries on them. Probably your best bet is an extra index for date.
Ref: http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html
"MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on"
Furthermore - you can test the way MySQL uses indexes by prefixing your queries with "explain", but you probably knew that already. This way you can try the variants and evaluate them.
Best Answer
First of all, your PRIMARY KEY depends on the logical nature of your data and will be indexed automatically by the DBMS. If the PK happens to be composite, it will automatically generate a composite index underneath it. The same goes for UNIQUE constraints and FOREIGN KEYs.
On top of that, under InnoDB, PK also acts as a clustered index.
As for the non-key fields, index based on the expected queries. Assuming your table T has several fields: A, B, C, D (etc.), for a query such as...
...create a composite index on {A, B} (or {B, A}), but not on C, D etc.
For the...
...you'll need a separate indexes on {A} and on {B}.
For both...
...you'll need indexes on {A, B} and {B} (or {B, A} and {A}).
For both...
...you'll only need an index on {A, B}.
For...
...you'll need an index on {A, B}.
Etc, etc...
Do not create indexes that you don't need, since they incur a penalty in storage space and in INSERT/UPDATE/DELETE performance. For more on indexing (and other database performance considerations), I warmly recommend reading: Use The Index, Luke!