mysql and indexes?

[Robert9]

Here is my next table:

- item_id => auto

- group_id;
the items can be in groups and they are fetched as groups (select * where group_id =x)

- another_id
items also are fetched by this _id

- title

- order

A similar example uses item_id/another_id as an index.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs.

My selects are:

find item by item_id (unique)
find item by content_id (unique)
find items by group_id (some)

item_id / content_id is unique for both fields
item_id / group_id is unique, but we can have some items with same group_id

From this I come to:

index: item_id / content_id
index: group_id

or should I use:

index: item_id / content_id
index: item_id / group_id

or

index: content_id
index: group_id

 

[Robert9]

Hmmm, when item_id and another_id are both unique,
why should I use them both at all?

Instead of item_id as auto_inc I could use content_id as my key_value, without auto_inc.

And if I think so ... I could delete the table and add fields group_id and title and order to content_table.
But this means three new fields. Do I want three new fields in post or thread?


But ... maybe I want to add some more things later, so I stay with the new table.

Then the question is now again?

index: item_id / content_id
index: group_id
-
index: item_id / content_id
index: item_id / group_id
-
index: content_id
index: group_id