Double indexes on one field

[Yannick Warnier]

Hi all,

Using PhpMyAdmin, I seldom get the warning message:
PRIMARY and INDEX keys should not both be set for column `ID`

I understand its meaning, but I was wondering to what extent having a
field indexed AND being a primary key might slow down/speed up my
queries.

Is that gonna take twice the time if I am searching on the ID field,
just because there are two indexes?

I'd like to have a rough idea of how serioulsy I need to avoid these.

Thank you,

Yannick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Martijn Tonies]

> Using PhpMyAdmin, I seldom get the warning message:
> PRIMARY and INDEX keys should not both be set for column `ID`
>
> I understand its meaning, but I was wondering to what extent having a
> field indexed AND being a primary key might slow down/speed up my
> queries.
>
> Is that gonna take twice the time if I am searching on the ID field,
> just because there are two indexes?
>
> I'd like to have a rough idea of how serioulsy I need to avoid these.

Creating a PRIMARY KEY will automatically create an index.

Why would you create a second index for that field?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

[Alec....@quantel.com]

Yannick Warnier <ywar...@beeznest.org> wrote on 03/10/2005 11:18:05:

> Hi all,

>
> Using PhpMyAdmin, I seldom get the warning message:
> PRIMARY and INDEX keys should not both be set for column `ID`
>
> I understand its meaning, but I was wondering to what extent having a
> field indexed AND being a primary key might slow down/speed up my
> queries.
>
> Is that gonna take twice the time if I am searching on the ID field,
> just because there are two indexes?
>
> I'd like to have a rough idea of how serioulsy I need to avoid these.

It will not slow down your searches at all, but it will slow down your
inserts. Since a PRIMARY KEY is a UNIQUE index that happens to have been
declared to be primary, you are simply storing the same information twice.
I cannot think of any possible benefit in having two identical indexes on
a table, and there is a cost to maintaining two index trees.

Alec

[Yannick Warnier]

Le lundi 03 octobre 2005 =E0 12:29 +0200, Martijn Tonies a =E9crit :

> > Using PhpMyAdmin, I seldom get the warning message:
> > PRIMARY and INDEX keys should not both be set for column `ID`
> >
> > I understand its meaning, but I was wondering to what extent having a
> > field indexed AND being a primary key might slow down/speed up my
> > queries.
> >
> > Is that gonna take twice the time if I am searching on the ID field,
> > just because there are two indexes?
> >
> > I'd like to have a rough idea of how serioulsy I need to avoid these.

>=20

> Creating a PRIMARY KEY will automatically create an index.

>=20

> Why would you create a second index for that field?

I'm asking myself the same question. I am working on someone else's
database.

Thanks both,

Yannick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmy...@freebsd.csie.nctu.edu.tw

[Bastian Balthazar Bux]

Yannick Warnier wrote:

> Le lundi 03 octobre 2005 à 12:29 +0200, Martijn Tonies a écrit :
>
>>>Using PhpMyAdmin, I seldom get the warning message:
>>>PRIMARY and INDEX keys should not both be set for column `ID`
>>>
>>>I understand its meaning, but I was wondering to what extent having a
>>>field indexed AND being a primary key might slow down/speed up my
>>>queries.
>>>
>>>Is that gonna take twice the time if I am searching on the ID field,
>>>just because there are two indexes?
>>>
>>>I'd like to have a rough idea of how serioulsy I need to avoid these.
>>

>>Creating a PRIMARY KEY will automatically create an index.
>>

>>Why would you create a second index for that field?
>
>
> I'm asking myself the same question. I am working on someone else's
> database.
>
> Thanks both,
>
> Yannick
>
>

Is it a multi-field index ? some versions of phpmyadmin show that
messages also if only one field is duplicated.

example:

CREATE TABLE `tab_sint` (
`id_cns` tinyint(3) unsigned NOT NULL default '0',
`anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
`data_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
`particolare` mediumint(8) unsigned NOT NULL default '0',
`generale` mediumint(8) unsigned NOT NULL default '0',
UNIQUE KEY `idx_cns_gen_anno`
(`anno_dep`,`id_cns`,`generale`,`particolare`),
UNIQUE KEY `idx_cns_par_anno`
(`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=87189031 ;

This schema issue the warning on 'id_cns' but really make sense having 2
indices here since they serves different kind of querys and constraints.

Regards,
Francesco

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=myo...@freebsd.csie.nctu.edu.tw

[Yannick Warnier]

Le lundi 03 octobre 2005 =E0 16:24 +0200, Bastian Balthazar Bux a =E9crit :
> Yannick Warnier wrote:
> > Le lundi 03 octobre 2005 =E0 12:29 +0200, Martijn Tonies a =E9crit :
> >=20

> >>>Using PhpMyAdmin, I seldom get the warning message:
> >>>PRIMARY and INDEX keys should not both be set for column `ID`
> >>>
> >>>I understand its meaning, but I was wondering to what extent having a
> >>>field indexed AND being a primary key might slow down/speed up my
> >>>queries.
> >>>
> >>>Is that gonna take twice the time if I am searching on the ID field,
> >>>just because there are two indexes?
> >>>
> >>>I'd like to have a rough idea of how serioulsy I need to avoid these.
> >>
> >>Creating a PRIMARY KEY will automatically create an index.
> >>
> >>Why would you create a second index for that field?

> >=20
> >=20

> > I'm asking myself the same question. I am working on someone else's
> > database.

> >=20
> > Thanks both,
> >=20
> > Yannick
> >=20
> >=20
>=20
>=20

> Is it a multi-field index ? some versions of phpmyadmin show that
> messages also if only one field is duplicated.

>=20
> example:
>=20
>=20

> CREATE TABLE `tab_sint` (
> `id_cns` tinyint(3) unsigned NOT NULL default '0',
> `anno_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
> `data_dep` smallint(4) unsigned zerofill NOT NULL default '0000',
> `particolare` mediumint(8) unsigned NOT NULL default '0',
> `generale` mediumint(8) unsigned NOT NULL default '0',
> UNIQUE KEY `idx_cns_gen_anno`
> (`anno_dep`,`id_cns`,`generale`,`particolare`),
> UNIQUE KEY `idx_cns_par_anno`
> (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)

> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 PACK_KEYS=3D1 AUTO_INCREMENT=3D8=
7189031 ;
>=20
>=20

> This schema issue the warning on 'id_cns' but really make sense having 2
> indices here since they serves different kind of querys and constraints.

No, no, it's just a dumb double index :-)

I am pretty sure it is completely useless. Actually I have already
removed it.

Yannick

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmy...@freebsd.csie.nctu.edu.tw