help with indexing

[spiri7u]

Hi everyone.

I have created my database and all of the rows. The first row i created as the id with a primary index.

After that i wrote out the rest and selected index as the index. I think this is what has thrown up the error

#1070 - Too many key parts specified; max 16 parts allowed

However i am unsure what else to set them as i mean i have primary, unique, index and fulltext. The information being stored is for a combat system and it has the likes of health, energy, money, level, rank etc and honestly i am slightly confused.

Can anyone shed some light on what i should be indexing these as because i could pick unique but then i read that it apparently means unique to that row so would that be an issue if say fire and water where the same value?.

Sorry if this sounds noobish, i am still learning

[NigelRen]

Do you have a table structure - preferably SQL?
Also you say 'selected index as the index' which I'm not sure what you mean by this? Are you using phpmyadmin or some other database tool?
What fields do you need to be indexed?

[freshnet]

the primary key value for your table is a value that's used to identify the row. That one should have the primary index, and only one of those is allowed per table.
If you have some other data in your table that's unique (for example a character name or in-game id number) you can also use a unique index for that. A unique index will give an error if you try to insert a duplicate value.
For all other information, you can use a regular index. It's generally only worth indexing columns that you will be using a lot, so if you're looking up things by a number such as health, index that.

In general though, with a mysql database the performance improvement you'll see with indexing will be very small until the database tables get quite large (e.g. around 1 million rows).

[johnj]

There is a limit on the number of key parts that can be specified. I am not sure if we can override this - You need to ask this question on the mysql forum.

[freshnet]

For your needs I don't see why you'd need a multi-part index. Since these are all separate statistics, I'm guessing you'd be querying many of them separately. If not, just index the few most commonly queried columns together. A multi-column index is slightly faster than a single column, but again unless your tables are into the millions of rows, you probably won't see any difference.