MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<vpendleton Date:February 11 2004 6:10pm
Subject:Re: Indexing Woes
View as plain text  
Yes a duplicate copy of the table is created and the Indexes are created 
on that new table, the original table is dropped and the new table is 
renamed. What type of data is being indexed? Are you attempting to index 
while the database is being actively used? What version of MySQL are you 

>>>>>>>>>>>>>>>>>> Original Message
> <<<<<<<<<<<<<<<<<<

On 2/11/04, 11:29:44 AM, Chris Fossenier <chris@stripped> wrote 
regarding Indexing Woes:

> Hello,

> I had a question about indexing a while back and everyone screamed
> "normalize"!!

> Well...I've normalized much as I'm going to, and at most I have 3
> indexes on
> any one table. My database has 120 million records in it and the index
> creation is taking a ridiculous amount of time. I can create the same
> indexes on MS SQL or Oracle in a fraction (a small fraction) of the
> time.

> Any tips? If I look at the PROCESSLIST, I can see that MySQL is using
> Key
> Cache instead of File Sort. I've read that File Sort is faster but have
> no
> idea how to force MySQL to use this method.

> When MySQL indexes, does it actually create a copy of the table first
> (same
> size as original .MYD) and then prune it back to a smaller size for the
> .MYI? The reason I ask is because one table that I'm indexing has been
> running for a long time and the .MYI is only 3GB and the .MYD is
> 12GB....not
> a good sign.

> Is PostgreSQL better for indexing?

> I have a couple of tables with 1 or 2 indexes and they completed in 1 to
> 2hrs (same number of records). A couple of these inexes have larger
> fields,
> for example VARCHAR(30) is the largest field in any index.

> I also tried making a multi-part index "KEY idx_a1 (col1, col2, col3)"
> ...
> that doesn't seem to be running much faster.

> Let me know if anyone can help. There is nothing fancy about these
> tables,
> like I said the largest field is an address field at 30 chars....most
> others
> are ENUMs or 2-4 chars wide. My largest table has 30 columns (it indexed
> very fast because it only had one index) and my smallest table has 3
> columns.

> Thanks.

> Chris.
Indexing WoesChris Fossenier11 Feb
  • Re: Indexing Woesvpendleton11 Feb
    • RE: Indexing WoesChris Fossenier11 Feb
  • Re: Indexing WoesPeter Zaitsev11 Feb
    • RE: Indexing WoesChris Fossenier11 Feb
      • RE: Indexing Woesvpendleton11 Feb
        • RE: Indexing WoesChris Fossenier11 Feb
          • RE: Indexing Woesvpendleton12 Feb
            • RE: Indexing WoesChris Fossenier12 Feb
              • RE: Indexing Woesvpendleton12 Feb
                • RE: Indexing WoesChris Fossenier12 Feb