From: Hank
Date: September 19 2011 2:47pm
Subject: Re: myisamchk error (duplicate key records)
List-Archive: http://lists.mysql.com/mysql/225765
Message-Id: <CAPs4VGn-M5+VvTqULY_SF7ivW-yoSZQfy81MHhLKFz8+pcsP0g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=001485f6cd3eb005d704ad4c6a0f

--001485f6cd3eb005d704ad4c6a0f
Content-Type: text/plain; charset=ISO-8859-1

>
> > Exactly - I can't create an index on the table until I remove the
> > duplicate records.
>
> I was under the impression you were seeing this during a myisamchk run -
> which indicates you should *already* have a key on that field. Or am I
> interpreting that wrong?
>
>
I'm trying to rebuild an index after disabling all keys using myisamchk and
adding all 144 million records, so there is no current index on the table.

> I suppose I could create a non-unique index on the key
> > fields, and try that, but then I'd have to (1) create that index, and
> then (2) do
> > the full table scan query.
>
> No, if you create the index, you will not do a full tablescan afterwards.
> That's the whole point of an index :-)
>
>
But in order to create the index, mysql has to do a full table scan AND a
sort, which for 144 million records, is going to take a very long time.  So
an un-indexed full table scan without an index (i.e ..."HAVING count(*)>1")
 will actually take less time.

So like I said, it would be intuitive and helpful if the tool finding the
dup records actually provided enough info to view, find, and fix them so I
don't have to index, sort, or table scan the table a second time.

--001485f6cd3eb005d704ad4c6a0f--