MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Peter Zaitsev Date:February 11 2004 8:02pm
Subject:Re: Indexing Woes
View as plain text  
On Wed, 2004-02-11 at 09:29, Chris Fossenier wrote:
> 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.
>  

Check myisam_max_sort_file_size,  myisam_max_extra_sort_file_size and
myisam_sort_buffer  description and values. 

You shall be able to make Repair happening by Sort unless it is unique
index, which is much faster.


-- 
Peter Zaitsev, Senior Support Engineer
MySQL AB, www.mysql.com

Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL)
  http://www.mysql.com/uc2004/

Thread
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