List:General Discussion« Previous MessageNext Message »
From:matt ryan Date:July 22 2004 12:19pm
Subject:Re: Mysql growing pains, 4 days to create index on one table!
View as plain text  
>Split the myisam table into seperate tables.  We will
>call each table a bucket.
>
>Create a MERGE table of all of them. For selecting the
>data.
>
>When inserting, use a hash function on your primary
>key values to determine which bucket to insert into. 
>If you almost always select by primary key, then you
>can optimize your select queries to only look at the
>correct bucket as well, and to only use the merge
>table when you aren't selecting by primary key.
>
>This will speed your inserts because instead of 258M
>rows to search through for each insert there are only
>8M if you use 32 buckets.  The biggest benefit is that
>you could also insert in parallel using multiple mysql
>connections, because you could calculate the bucket as
>an additional column in your temporary table, then do
>the inserts for all the buckets the same time.
>  
>
I hit duplicate hashes for unique records, not sure why, I think I used 
aes_encrypt, how do you recomend creating a hash column via sql?

I already split the data into separate tables, and use a merge, it 
really didnt speed things up that much
Thread
Mysql growing pains, 4 days to create index on one table!matt ryan30 Jun
  • Re: Mysql growing pains, 4 days to create index on one table!Egor Egorov1 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Tim Brody15 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
      • RE: Mysql growing pains, 4 days to create index on one table!Donny Simonton15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Tim Brody15 Jul
RE: Mysql growing pains, 4 days to create index on one table!Lopez David E-r9374c30 Jun
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udikarni1 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan14 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart14 Jul
      • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
        • Re[2]: Mysql growing pains, 4 days to create index on one table!Pete McNeil15 Jul
          • Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
            • Re[2]: Mysql growing pains, 4 days to create index on one table!adburne15 Jul
              • Re[3]: Mysql growing pains, 4 days to create index on one table!Alejandro D. Burne15 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
  • RE: Mysql growing pains, 4 days to create index on one table!Lachlan Mulcahy16 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni15 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart15 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni15 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan16 Jul
Re: Mysql growing pains, 4 days to create index on one table!Udi.S.Karni16 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
Re: Mysql growing pains, 4 days to create index on one table!matt ryan16 Jul
RE: Mysql growing pains, 4 days to create index on one table!Lopez David E-r9374c16 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
  • Re: Mysql growing pains, 4 days to create index on one table!gerald_clark21 Jul
    • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart21 Jul
      • Re: Mysql growing pains, 4 days to create index on one table!matt ryan21 Jul
        • Re: Mysql growing pains, 4 days to create index on one table!Justin Swanhart21 Jul
          • Re: Mysql growing pains, 4 days to create index on one table!matt ryan22 Jul