indexing - Are these MySQL indexes duplicates? -

i'm little confused on best indexing options table. want searching , joining on both serviceid , tagid. need make sure 2 combined unique.

create table if not exists `service-tag` (   `id` int(10) unsigned not null auto_increment,   `serviceid` int(10) unsigned not null,   `tagid` int(10) unsigned not null,   primary key (`id`),   unique key `serviceid` (`serviceid`,`tagid`),   key `tagid` (`tagid`) ) engine=myisam  default charset=latin1 auto_increment=39 ; 

is adding second index on tagid necessary or automatically indexed being 2nd column in unique index?

if have multi-column index (as serviceid index is) mysql cannot use index when you're searching on tagid. in lay-language, mysql cannot "reach" index w/o using serviceid when need tagid.

thus, if plan on using tagid in statement want keep index.

if never plan on querying on tagid , of queries use both serviceid , tagid, yes, tagid-only index not needed.

this behavior documented in manual:

https://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

if table has multiple-column index, leftmost prefix of index can used optimizer rows. example, if have three-column index on (col1, col2, col3), have indexed search capabilities on (col1), (col1, col2), , (col1, col2, col3).

mysql cannot use index perform lookups if columns not form leftmost prefix of index.