Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: redundant indexes?

    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:


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

  2. #2
    Join Date
    Sep 2003
    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`),
    ) 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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts