MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

Question

I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found.

Commands ran:

CREATE TABLE mytable (id1 int, id2 int, score float) ENGINE=MyISAM;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ADD INDEX id1_index (id1);

SELECT COUNT(*) FROM mytable; # returns 50 billion
SELECT COUNT(DISTINCT id1) FROM mytable; # returns 50K, should be about 50M
SELECT COUNT(*) FROM mytable WHERE id1 = 49302; # returns 0 results

ALTER TABLE mytable DISABLE KEYS;
SELECT * FROM mytable WHERE id1 = 49302 LIMIT 1; # returns 1 row

Is this a bug with MySQL, or does this behavior make sense for some reason?

Note: When I ran ALTER TABLE mytable ENABLE KEYS; just now, the command is acting like it is building an index for the first time (it's still running after 30 minutes, and memory usage is at 80 GB, which matches my setting of myisam_sort_buffer_size=80G. I'll reply when this command finishes running (the original ALTER .. ADD INDEX.. took 7.5 hours, so it may be a bit).

Update: Running SHOW PROCESSLIST indicates "Repair with keycache" is taking place with my ENABLE KEYS command.

Update 2: I killed the repair job on the original index because after several hours, the memory and IO seemed pretty constant, and I hoped if I started over, it may just work. So in second pass, I rebuilt the table and index, and after doing so, the exact same result occurs.

As requested, here is explain for count queries with index enabled:

mysql> explain select * from mytable where id1 = 49302;
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | mytable   | ref  | id1_index     | id1_index | 5       | const |    1 | Using where |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(DISTINCT id1) FROM mytable;
+----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+
| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows      | Extra                    |
+----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+
|  1 | SIMPLE      | mytable   | range | NULL          | id1_index | 5       | NULL | 170331743 | Using index for group-by |
+----+-------------+-----------+-------+---------------+-----------+---------+------+-----------+--------------------------+
1 row in set (0.01 sec)

Here is explains after disabling indexes (Note: 25 billion is correct number of records in table, not 50 billion as mentioned above):

mysql> explain select * from mytable where id1 = 66047071;
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows        | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+
|  1 | SIMPLE      | mytable   | ALL  | NULL          | NULL | NULL    | NULL | 25890424835 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT COUNT(DISTINCT id1) FROM mytable;
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows        | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+
|  1 | SIMPLE      | mytable   | ALL  | NULL          | NULL | NULL    | NULL | 25890424835 |       |
+----+-------------+-----------+------+---------------+------+---------+------+-------------+-------+
1 row in set (0.00 sec)

Update 3: Still hoping to solve this oddity. Is there something I can do with myisamchk that might fix this? Since I completely repopulated the table and rebuilt the index (i.e. starting from scratch) and got the same result, I assume this was not just some freak occurrence, and that it is due to some internal limit I'm unaware of. On a side note, I've tried switching to Postgres for this dataset, but running into some other unrelated issues (that I'll leave to a different question), so fixing this index is still a top priority for me. Thanks!!

Update 4: Running CHECK TABLE currently. Will post back with updates as I have them

Answer 1

OBSERVATION #1

You have this:

CREATE TABLE mytable (id1 int, id2 int, score float) ENGINE=MyISAM;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ADD INDEX id1_index (id1);

This is how you should load the MyISAM table:

CREATE TABLE mytable (id1 int, id2 int, score float,key (id1)) ENGINE=MyISAM;
ALTER TABLE mytable DISABLE KEYS;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ENABLE KEYS;

When you do disable keys, it stops the .MYI file from updating nonunique indexes.

During the LOAD DATA INFILE, the .MYI file will not grow because it contains no unique indexes or a primary key. This will promarily focus on loading the .MYD.

The ENABLE KEYS phase will do a read pass through the .MYD and linearly build all the nonunique indexes. In your case, it will build the id1 index.

OBSERVATION #2

As for the bug you seem to be experiencing, think of this:

• 25 billions rows
• 4 byte integer for id1
• 100 billion bytes = 93.13G
• That's bigger than the 80G myisam_sort_buffer_size

IMPLICATIONS

• It is possible that incomplete or aborted indexing operations could have lost index pages that should have been written to the .MYI.
• It is also possible that doing DISABLE KEYS makes your query run because the id1 index is somehow being ignored. This could be the case since the EXPLAIN plan says type ALL and Possible Keys NULL. That's a full table scan. The SELECT query will work.
• With the keys enabled and incompletely written, some nonleaf node info being missing would lead to SELECT queries that are destined to fail because some of the id1 values are not among the nonleaf node in the .MYI.

SUGGESTIONS

Try running the LOAD DATA INFILE with my proposed code

CREATE TABLE mytable (id1 int, id2 int, score float,key (id1)) ENGINE=MyISAM;
ALTER TABLE mytable DISABLE KEYS;
LOAD DATA INFILE '50-billion-records.txt' INTO mytable (id1, id2, score);
ALTER TABLE mytable ENABLE KEYS;

Also, raise myisam_sort_buffer_size to 100G

Give it a Try !!!

UPDATE 2013-08-12 21:37 EDT

Repair By KeyCache is the result of hitting the max sort_buffer_size and myisam_sort_file_size. In turn, MySQL elects to sift through keys in the keycache.

There are three(3) options you could further adjust

OPTION #1

Set the sort_buffer_size to 16K. That's the minimum value allowed.

OPTION #2

Set the tmp_table_size to 1K. That's the minimum value allowed.

OPTION #3

Set the myisam_sort_buffer_size to its max value of 9223372036854775807

This should further prevent the Repair By KeyCache problem

Give it a Try !!!