Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

help with indexing

Codes here !

Moderators: macek, egami, gesf

help with indexing

Postby spiri7u » Sun Aug 05, 2012 2:20 pm

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 :)
spiri7u
New php-forum User
New php-forum User
 
Posts: 1
Joined: Sun Aug 05, 2012 2:15 pm

Re: help with indexing

Postby NigelRen » Mon Aug 06, 2012 12:07 am

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?
NigelRen
php-forum Active User
php-forum Active User
 
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: help with indexing

Postby freshnet » Mon Aug 06, 2012 4:06 am

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).
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 252
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Re: help with indexing

Postby johnj » Tue Aug 07, 2012 5:52 am

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.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: help with indexing

Postby freshnet » Tue Aug 07, 2012 10:43 am

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.
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 252
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron