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

[Donny Simonton]

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

[Garvin Hicking]

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]

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

[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]

Logged In: YES
user_id=326580
Originator: NO

fixed in trunk, will be in 2.11