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]
