What is the meaning of the "equal or removed indexing" error in MySQL?

Question

What is the meaning of the "equal or removed indexing" error in MySQL?

Navigation

#1 by (7 votes)

0

I'm working with small relationships and I noticed that in a state table where I've related the id field to the país[id] table I get this error or alert message, I'm not sure. Already in a relationship of table cidade to table estado[id] I do not have this error.

Can you help me to know why this error occurs in the estado - país?

My SQL is this:

$criar      = "CREATE TABLE IF NOT EXISTS $aonde.$tablee (
        id          INT(6)  UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
        nome        VARCHAR(75) DEFAULT NULL,
        uf          VARCHAR(5) DEFAULT NULL,
        pais        INT(6) UNSIGNED ZEROFILL DEFAULT NULL, 
                    PRIMARY KEY (id), KEY fk_Estado_pais (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28";

php mysql sql

asked by anonymous 19.10.2014 / 15:54

1 answer

Width of a td is not the last size PHP print capitalized names

score 7 · Answer 1

This error indicates that you have two index keys that are the same, where essentially you are repeating data in indexing the table. MySQL warns why duplicate indexing ultimately affects query performance.

In your SQL the duplication in question is visible:

PRIMARY KEY (id), KEY fk_Estado_pais (id)

You have your primary table key in the id field and then you have a new index key in the same id field with the name fk_Estado_pais .

Already the error you get:

PRIMARY indexing and fk_Post_State seem to be the same or one of them may have been removed.

It seems to me wrong to translate to your original because the information given by it does not match the information in the index table or the information in your SQL. But it is an assumption because it is only with the MySQL code associated with this error that I can know the original details of it.

Solution

For indexing according to your description, using only PRIMARY KEY(id) is more than sufficient given that it is an indexed and unique value that allows an excellent relationship with the other table.