MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Chris Fossenier Date:February 11 2004 8:06pm
Subject:RE: Indexing Woes
View as plain text  
What type of Data is being indexed?
 - depends on the table, but I'll provide a few samples.
 - table a1 has 3 indexes
      - col1 = BIGINT
      - col2 = varchar(3)
      - col3 = varchar(11)
 - table h1 has 3 indexes
      - col1 = varchar(11)
      - col2 = INT
      - col3 = INT

Is the database being used? 
 - no. It's a Quad Xeon machine attached to an EMC fibre storage system. My
iostat indicates the disks are hardly moving and my top shows that very
little CPU is being used.

What version of MySQL?
 - 4.0.17


-----Original Message-----
From: vpendleton@stripped [mailto:vpendleton@stripped] 
Sent: Wednesday, February 11, 2004 12:10 PM
To: Chris Fossenier
Cc: 'MySQL List'
Subject: Re: Indexing Woes

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