Double indexes on one field
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/[EMAIL PROTECTED]
Re: Double indexes on one field
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double indexes on one field
Yannick Warnier [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double indexes on one field
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double indexes on one field
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 '', `data_dep` smallint(4) unsigned zerofill NOT NULL default '', `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/[EMAIL PROTECTED]
Re: Double indexes on one field
Le lundi 03 octobre 2005 à 16:24 +0200, Bastian Balthazar Bux a écrit : 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 '', `data_dep` smallint(4) unsigned zerofill NOT NULL default '', `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. 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/[EMAIL PROTECTED]
