Hello. I have a MySQL database in which some of the tables have 2 indexes on the same column. Here's an example of what I mean:

CREATE TABLE `posts` (
`pid` int(10) unsigned NOT NULL auto_increment,
`message` text NOT NULL,
PRIMARY KEY (`pid`),
KEY `pid` (`pid`)
) TYPE=MyISAM;

In an attempt to make the database more efficient, someone added more indexes, even on columns that were already primary keys.

What effect does this have on the database? If the extra keys aren't helping, can I safely delete them without affecting my data? For example:

ALTER TABLE posts DROP INDEX pid;

Thanks in advance. BTW, nice forums you've got here!

That is odd. If anything, it would seem like it would slow it down (mysql optimizer has to think about what to do with extra indices?). My suggestion is create the table like so:

CREATE TABLE `posts` (
`pid` int unsigned NOT NULL auto_increment,
`message` text,
PRIMARY KEY (`pid`),
INDEX(pid)
) TYPE=MyISAM;

I removed the not null "constraint" from the text portion because you will most likely be using asp/php/perl to check to see if it's empty before you allow the post anyway... (and you're checking the return value from the insert anyway).