#1535 (ok 2.11) More than one INDEX key was created for column

Latest_Git
invalid
Interface (555)
1
2013-06-11
2004-12-11
No

The below table is a perfectly correct table, but
phpmyadmin is showing this:
"More than one INDEX key was created for column
`myInt`"

CREATE TABLE `adams_db` (
`myString` varchar(255) NOT NULL,
`myInt` int(11) NOT NULL,
`myInt2` int(11) NOT NULL,
`key` bigint(20) NOT NULL auto_increment,
PRIMARY KEY (`key`),
KEY `myInt2` (`myInt2`),
KEY `myInt` (`myInt`,`key`),
KEY `myInt_2` (`myInt`,`myInt2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
AUTO_INCREMENT=1 ;

I really like the idea, but it needs to take into all of the
keys into consideration.

Donny

Discussion

  • Garvin Hicking

    Garvin Hicking - 2004-12-11

    Logged In: YES
    user_id=473563

    Did you make MySQL benchmark tests to check, if adding
    single keys AND combined keys is faster than only having set
    the single keys?

    I think I somehwere heard that if you add single keys on a
    column, setting a combined key with those already added
    single keys does not speed up things for MySQL.

    The algorithm to tune the error detection onf the index key
    warnings is quite modular and easy to tweak. We only need to
    define those rules... :-)

     
  • Donny Simonton

    Donny Simonton - 2004-12-11

    Logged In: YES
    user_id=6296

    Let me give a better example not using the table above, it
    was a table that I saw on the phpmyadmin demo site using
    the latest CVS.

    Let's take a table like the one below.
    CREATE TABLE `IPBots2` (
    `remote_addr` char(20) NOT NULL default '',
    `public_ip` char(20) NOT NULL default '',
    `user_agent` char(128) NOT NULL default '',
    `cc` char(2) NOT NULL default '',
    `seen` int(10) unsigned NOT NULL default '0',
    `banned` enum('N','Y','G') NOT NULL default 'N',
    `updated` timestamp NOT NULL default
    CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY (`remote_addr`,`public_ip`,`user_agent`),
    KEY `cc_banned` (`cc`,`banned`),
    KEY `public_ip` (`public_ip`),
    KEY `cc_seen` (`cc`,`seen`),
    KEY `cc` (`cc`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    It's a table that we use to keep track of IP addresses and IP
    addresses that we have banned. It also has a cc code,
    country code, like US, CA, RU, whatever. This is where we
    will focus.

    If you write a query like with the above table,
    select user_agent from IPBots where cc = 'US';
    you would think that mysql would use the cc index, but it
    won't, as seen below.

    mysql> EXPLAIN SELECT user_agent
    -> FROM IPBots2
    -> WHERE cc = 'US';
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys |
    key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    | 1 | SIMPLE | IPBots2 | ref | cc_banned,cc_seen,cc |
    cc_banned | 2 | const | 7867 | Using where |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    1 row in set (0.00 sec)

    It will use one of the indexes that has cc at the beginning.
    This is because the combination is better than the single
    index.

    so first of all based on this, the single cc index is worthless
    so far.

    Now let's try some other ones.
    mysql> EXPLAIN SELECT user_agent FROM IPBots2 WHERE cc
    = 'US' and banned = 'Y';
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------------+------+-------------
    +
    | id | select_type | table | type | possible_keys |
    key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------------+------+-------------
    +
    | 1 | SIMPLE | IPBots2 | ref | cc_banned,cc_seen,cc |
    cc_banned | 3 | const,const | 2411 | Using where |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------------+------+-------------
    +
    1 row in set (0.00 sec)

    Again it uses cc_banned, but it really should, since
    cc_banned is a combination of banned and cc.

    And one that uses another index.
    mysql> EXPLAIN SELECT user_agent FROM IPBots2 WHERE cc
    = 'US' and seen > 10000;
    +----+-------------+---------+-------+--------------------
    --+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys |
    key | key_len | ref | rows | Extra |
    +----+-------------+---------+-------+--------------------
    --+---------+---------+------+------+-------------+
    | 1 | SIMPLE | IPBots2 | range | cc_banned,cc_seen,cc
    | cc_seen | 6 | NULL | 21 | Using where |
    +----+-------------+---------+-------+--------------------
    --+---------+---------+------+------+-------------+
    1 row in set (0.00 sec)

    Which is a combination of cc + seen.

    Now let's say that we decide to write a query where we only
    want US that has been updated since midnight. Which index
    would it use? Since we don't have an index on cc + updated.

    mysql> EXPLAIN SELECT user_agent FROM IPBots2 WHERE cc
    = 'US' and updated > '2004-12-11 00:00:00';
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys |
    key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    | 1 | SIMPLE | IPBots2 | ref | cc_banned,cc_seen,cc |
    cc_banned | 2 | const | 7867 | Using where |
    +----+-------------+---------+------+---------------------
    -+-----------+---------+-------+------+-------------+
    1 row in set (0.00 sec)

    It takes the best one that it can.

    So personally, I would say that if somebody has a single index
    on something like cc, and then they add an index on cc +
    seen or whatever, then I would show the warning about
    something already existing.

    But I would also only do this on indexes, not on primary keys
    or unique keys. Because in many cases I could see where
    somebody wants something to be unique, but they add an
    index on a combination of fields.

    Just my 2 cents.

    Donny

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580

    as i know, and with current MySQL versions, donsim is right!

    phpMyAdmin should not nag about this

    with indexes like:

    (col1, col2)

    MySQL can not make any use of this index for col2, so a
    second index on col2 is required!

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580

    and even with two indexes like

    (id, cat, name)
    and
    (id, cat)

    its not implicitly an error, as searching the possible much
    much smaller index (id,cat) is much much faster than (id,
    cat, name) - but (id, cat, name) can be still necessary

    or with latest MySQL version the index can be comlete the
    same but of different type (btree, hash)

    or in future version the order can different from current ASC

    possible we should just alter the message beeing displayed
    and add an 'possible' and only if the index is complete the
    same not only one field in the index

     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580

    i think we should alter the message a little bit ... 2.7.1

     
  • Sebastian Mendel

    • summary: More than one INDEX key was created for column --> (2.7.1) More than one INDEX key was created for column
    • status: open --> open-postponed
     
  • Marc Delisle

    Marc Delisle - 2005-12-12
    • summary: (2.7.1) More than one INDEX key was created for column --> More than one INDEX key was created for column
     
  • Alexander Schuch

    Logged In: YES
    user_id=501368

    CREATE TABLE `irc_auth` (
    `network_id` tinyint(3) unsigned NOT NULL default '0',
    `member_id` smallint(5) unsigned NOT NULL default '0',
    `auth` varchar(32) character set utf8 NOT NULL default
    '',
    PRIMARY KEY (`network_id`,`member_id`),
    UNIQUE KEY `network_id` (`network_id`,`auth`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    I want to store the IRC account name (auth name) per IRC
    network and member. Every member can have one auth name,
    but an auth name must be unique per network, as no two
    member are allowed to share the same auth name per IRC
    network.

    So from the logic, this setup is fine, but phpMyAdmin still
    gives me a warning: "PRIMARY and INDEX keys should not both
    be set for column `network_id`".

     
  • Sebastian Mendel

    • assigned_to: nobody --> cybot_tm
    • status: open-postponed --> open-accepted
     
  • Sebastian Mendel

    • priority: 5 --> 1
    • summary: More than one INDEX key was created for column --> (ok 2.11) More than one INDEX key was created for column
    • status: open-accepted --> open-fixed
     
  • Sebastian Mendel

    Logged In: YES
    user_id=326580
    Originator: NO

    fixed in trunk, will be in 2.11

     
  • Marc Delisle

    Marc Delisle - 2007-08-21
    • status: open-fixed --> closed-fixed
     
  • Michal Čihař

    Michal Čihař - 2013-06-11
    • Status: closed-fixed --> invalid
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks