1

I have a very large table (15 billion rows) with a structure like this:

CREATE TABLE `textlines` (
  `PGUID` binary(16) DEFAULT NULL,
  `StatID` int(11) DEFAULT NULL,
  `Time` datetime(3) DEFAULT NULL COMMENT 'local time',
  `UTCOffset` float DEFAULT NULL COMMENT 'UTC offset: UTC time = Time (local) -
UTCOffset',
  `Text` varchar(255) DEFAULT NULL,
  KEY `TimerOrder` (`StatID`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

The table is loaded with a LOAD INFILE call on flat files. (I disable keys before loading the table and enable keys after). I have also tried creating the table without an index and adding the composite index (StatID, Time) after the table is loaded. Logs show no errors whatsoever. Once it is finished, if I query the data, e.g.,

>select count(*) from textlines where StatID=1014;

I get zero results returned (and no errors reported). I know the data is in the table, which I verify by e.g.,

>select * from textlines limit 10;

| PGUID         |  StatID   | Time                                | UTCOffset | Text                                      |
+---------------+-----------+-------------------------------------+-----------+---------------------------------+
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:14.009 |        -4 | Could the black cat   |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:17.010 |        -4 | collapse the limit.   |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:20.011 |        -4 | And never shall there be, |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:21.012 |        -4 | fast the pulse of america.     |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:23.013 |        -4 | retire to the front.      |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:26.014 |        -4 | Also tuna is delightful, |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:29.015 |        -4 | mark is an avid fisherman |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:30.016 |        -4 | chapter six scene eighteen     |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:33.017 |        -4 | would be detrimental to  |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:36.018 |        -4 | replace the last cog.                |
+---------------------------+-----------+-------------------------+-----------+---------------------------------+
10 rows in set (0.00 sec)

I have tried partitioning the table (by HASH(StatID)). Again the indices for each partition build fine, but the problem of missing rows when querying using the index persists. I have also tried reducing table size by importing only some of the data into the table (e.g., only 1 billion rows) -- again with the same results.

The issue seems to be similar to the one experienced here, as the results seem to be missing in groups.

I am using MySQL 5.6.19 x64 on Windows 2012 Server with 128GB RAM.

The strange thing is, the same approach described above seems to have worked with a somewhat smaller table (250 million rows) on an earlier version of MySQL (5.6.12) with no issues.

2
  • Please run SELECT table_rows,data_length,index_length FROM information_schema.tables WHERE table_name='textlines'; and post the output. – RolandoMySQLDBA Sep 11 '14 at 18:39
  • I ran the query on a smaller table experiencing the same issue (currently in the process of building an index again on the larger partitioned table) The output is: +------------+-------------+--------------+ | table_rows | data_length | index_length | +------------+-------------+--------------+ | 825463549 | 51866405392 | 16357521408 | +------------+-------------+--------------+ – Dylan Walker Sep 12 '14 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.