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
Chris.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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
using?
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2/11/04, 11:29:44 AM, Chris Fossenier <[EMAIL PROTECTED]> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
