List:General Discussion« Previous MessageNext Message »
From:Rick James Date:August 12 2013 7:53pm
Subject:RE: MyISAM index missing rows
View as plain text  
" certain rows can no longer be found " -- Do CHECK TABLE.  (It will take a loooong time.)
 It may tell you to REPAIR TABLE, which will also take a loooong time; but it will be
necessary.  (This is a strong reason for going to InnoDB.  But it will be 2x-3x bigger on
disk.)

> -----Original Message-----
> From: Dolan Antenucci [mailto:antenucci.d@stripped]
> Sent: Monday, August 12, 2013 10:26 AM
> To: mysql@stripped
> Subject: MyISAM index missing rows
> 
> Hi Everyone,
> 
> I have a MyISAM table with 25 billion rows (structure: <id1 int, id2 int,
> score float>), and after I create an index on id1, certain rows can no
> longer be found.
> 
> I've posted a detailed summary of my problem at dba.stackexchange.com, but
> haven't had success with finding a solution thus far.  Here's the URL to
> that post:
> http://dba.stackexchange.com/questions/47906/mysql-myisam-index-causes-
> query-to-match-no-rows-indexes-disabled-rows-match
> 
> As that post describes, one oddity is with an EXPLAIN I run on the same
> query with indexes enabled vs. disabled. When disabled, "rows" = 25
> billion; when enabled, "rows" = 170 million.  Based on this, I'm wondering
> if some restriction is causing only 170 million rows to index. (Of course,
> I could be completely misinterpreting this EXPLAIN result).
> 
> Here is my server version: 5.5.31-0ubuntu0.12.04.2 (Ubuntu), which should
> also be 64-bit (i've verified by running "file /usr/sbin/mysqld" (says
> "ELF
> 64 bit..")
> 
> Any help is greatly appreciated!  Just let me know if you need more
> details
> 
> Sincerely,
> 
> Dolan Antenucci
Thread
MyISAM index missing rowsDolan Antenucci12 Aug
  • RE: MyISAM index missing rowsRick James12 Aug