(ruid1, ruid2) will help for AND, but not at all for OR.
> -----Original Message-----
> From: Shawn Green [mailto:shawn.l.green@stripped]
> Sent: Wednesday, June 20, 2012 2:30 PM
> To: mysql@stripped
> Subject: Re: Indexing about 40 Billion Entries
>
> On 6/20/2012 5:45 AM, Christian Koetteritzsch wrote:
> > Hi guys,
> >
> > As the title says I'm trying to index 40 billion entries with two
> > indexes on a server with 16 cores and 128GB RAM. The table is the one
> > below and it is a myisam table. The *.myd file is about 640GB
> >
> > DROP TABLE IF EXISTS `l4_link`;
> > CREATE TABLE `l4_link` (
> > `ruid1` int NOT NULL,
> > `ruid2` int NOT NULL,
> > `overlap` int NOT NULL
> > );
> >
> > I need an index for ruid1 and for ruid2.
> >
>
> Actually, based on your proposed query, I believe you want an index on
> (ruid1, ruid2) not separate indexes for each column.
>
>
> > The status for this table is the following:
> >
> > Name: l4_link
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 39806500262
> > Avg_row_length: 17
> > Data_length: 676710504454
> > Max_data_length: 4785074604081151
> > Index_length: 1024
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2012-06-19 14:51:29
> > Update_time: 2012-06-19 16:26:35
> > Check_time: NULL
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options:
> > Comment:
> >
> > The variables for myisam are the following:
> > mysql> show global variables like '%myisam%';
> > +----------------------------------------+---------------------------
> ------------+
> >
> > | Variable_name |
> > Value |
> > +----------------------------------------+---------------------------
> ------------+
> >
> > | myisam_data_pointer_size | 6 |
> > | myisam_max_sort_file_size | 9223372036853727232 |
> > | myisam_mmap_size | 18446744073709551615 |
> > | myisam_recover_options | BACKUP |
> > | myisam_repair_threads | 1 |
> > | myisam_sort_buffer_size | 8388608
> |
> > | myisam_stats_method | nulls_unequal |
> > | myisam_use_mmap |
> > OFF |
> > +---------------------------------------+----------------------------
> ------------+
> >
> > 8 rows in set (0.00 sec)
> >
> > The temp folder has about 16tb free space.
> >
> > When I start the indexing process, it copies the 640Gb into a temp
> file
> > and then starts with "repair with keycache".
> > On the internet I found that if it says "repair with keycache" you
> shold
> > increase the "myisam_max_sort_file_size", but this didn't work.
> > It still says "repair with keycache" after it copied the data to the
> > temp dir.
> >
> > I hope you have any idea how to fix this.
> >
>
> Try maxing out the following settings:
>
> myisam_sort_buffer_size
> key_buffer_size
>
> http://dev.mysql.com/doc/refman/5.5/en/server-system-
> variables.html#sysvar_myisam_sort_buffer_size
>
> http://dev.mysql.com/doc/refman/5.5/en/server-system-
> variables.html#sysvar_myisam_sort_buffer_size
>
> You will need to experiment with how large the maximum value will be
> permitted for your platform.
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
