mysql indexes - how to use cardinality?

[Anon]

I manage my mysql dbases using phpmyadmin. I have added indexes (indices?) to my tables a while back, and thought nothing more of it... till now.

There is a section in the index section of a table in phpmyadmin which shows the 'cardinality' value. This went straight over my head before, but I've pinpointed it as the cause of my problem - I have a table with a cardinality index of 122 (I didn't set that) and now when I try to add a new entry to the table (currently at 122 entries) it throughs back a sql error that the primary id is already present.

I looked up cardinality in the sql manual and it looks like something to limit the max number of entries. Why? Does this mean I have to add the indexes ONLY when the table is finished (is it ever?). How can I have the cardinality change dynamically...

I need to have indexes AND have a table I can add entries to without worrying about whether it is full

[Anon]

For what you explain, your index must be an auto-increment field. An index is a way to make sure you don't insert the data twice in a table. When using an auto-increment field, you don't have to give a value to it when using INSERT, because the db is clever enough to add +1 to it.

Now, the problem. Imagine this table

id name surname
1 Ludwig Beethoven
2 Johann Strauss
3 Amadeus Mozart

id is an auto-numeric field. For making an insertion of a new row, you use the following syntax:

INSERT INTO musicians (name,surname) VALUES ('Joe','Satriani')

You don't have to specify that the new index is 4. The db knows that already.

Now imagine you delete the Strauss entry. The cardinality of the table is STILL 4, and the next row you insert will have the id = 5

Why? Well, in case you had this id in another table (imagine music_compositions), you would like to know to who belongs the composition. If you alter the id field in the musicians table, you would lose your db integrity, because what used to be Beethoven's could now be from Mozart.

I hope i have explained myself clearly. If not, please say so, and i'll try better. Also, my mail is alejandro_alac.es (_ for @). Send me a mail if you want an example of keys and cardinality in tables.

fLIPIS

[Anon]

so if I understand, cardinality values are dynamic depending on the number of entries?
I understand the primary key, but I am getting an error when trying to insert a new row...

basically, I am entering nothing in the primary key field, but trying to add a new row by inserting data into a varchar field -
the table has a cardinality of 122, and the next primary key value should be 128 (difference is due to deletions of some rows).

However, whenever I try to add a new field, I now get the error:

Duplicate entry '127' for key 1

I didn't specifiy 127 for the primary key, I left it blank. I have never had this problem before (for this table of 121 entries, or others with 2000+ entries). I just suddenly started getting it. And I don't understand why.

Any suggestions?

[Anon]

oops, solved the problem by my own post - the value 127 already taken got some bells ringing on the size of tinyint...

I had set up the primary key as tinyint back in the days when I was still wet behind the ears in thinking tinyint with length of 4 == 9999 entries. Never noticed the problem after I had actually RFM... my other tables were had primary keys of type mediumint.

Changed them all back to int and everything is back to normal.

[Anon]

:-)

Now that's a very common mistake. I did it 'till it happened the same to me. Now i create the auto-nums as INT always.

Glad to help

fLIPIS