List:General Discussion« Previous MessageNext Message »
From:Yannick Warnier Date:October 3 2005 2:52pm
Subject:Re: Double indexes on one field
View as plain text  
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 '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.

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

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

Yannick

Thread
Double indexes on one fieldYannick Warnier3 Oct
  • Re: Double indexes on one fieldMartijn Tonies3 Oct
    • Re: Double indexes on one fieldYannick Warnier3 Oct
      • Re: Double indexes on one fieldBastian Balthazar Bux3 Oct
        • Re: Double indexes on one fieldYannick Warnier3 Oct
  • Re: Double indexes on one fieldAlec.Cawley3 Oct