title: Nothing returned if the column with the WHERE condition has an index
body: <p>Working on Debian 11 (Bullseye) first with the distribution's MariaDB version 10.5 and now with the version 10.6.7 from MariaDB repositories.</p>
  <p>I'm failing to get correct indexes for some big tables from the dump of a genetics database (ensembl homo_sapiens_variation_106_37) from here: <a href="ftp://ftp.ensembl.org/pub/grch37/release-106/mysql/homo_sapiens_variation_106_37/" rel="nofollow noreferrer">ftp://ftp.ensembl.org/pub/grch37/release-106/mysql/homo_sapiens_variation_106_37/</a></p>
  <p>The one table is variation_feature:</p>
  <pre><code>CREATE TABLE `variation_feature` (
    `variation_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `seq_region_id` int(10) unsigned NOT NULL,
    `seq_region_start` int(11) NOT NULL,
    `seq_region_end` int(11) NOT NULL,
    `seq_region_strand` tinyint(4) NOT NULL,
    `variation_id` int(10) unsigned NOT NULL,
    `allele_string` varchar(50000) DEFAULT NULL,
    `ancestral_allele` varchar(50) DEFAULT NULL,
    `variation_name` varchar(255) DEFAULT NULL,
    `map_weight` int(11) NOT NULL,
    `flags` set('genotyped') DEFAULT NULL,
    `source_id` int(10) unsigned NOT NULL,
    `consequence_types` set('intergenic_variant','splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','TFBS_ablation','TFBS_fusion','TFBS_amplification','TFBS_translocation','regulatory_region_ablation','regulatory_region_fusion','regulatory_region_amplification','regulatory_region_translocation','feature_elongation','feature_truncation','regulatory_region_variant','TF_binding_site_variant','protein_altering_variant','start_retained_variant') NOT NULL DEFAULT 'intergenic_variant',
    `variation_set_id` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64') NOT NULL DEFAULT '',
    `class_attrib_id` int(10) unsigned DEFAULT '0',
    `somatic` tinyint(1) NOT NULL DEFAULT '0',
    `minor_allele` varchar(50) DEFAULT NULL,
    `minor_allele_freq` float DEFAULT NULL,
    `minor_allele_count` int(10) unsigned DEFAULT NULL,
    `alignment_quality` double DEFAULT NULL,
    `evidence_attribs` set('367','368','369','370','371','372','418','421','573','585') DEFAULT NULL,
    `clinical_significance` set('uncertain significance','not provided','benign','likely benign','likely pathogenic','pathogenic','drug response','histocompatibility','other','confers sensitivity','risk factor','association','protective','affects') DEFAULT NULL,
    `display` int(1) DEFAULT '1',
    PRIMARY KEY (`variation_feature_id`),
    KEY `pos_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`),
    KEY `variation_idx` (`variation_id`),
    KEY `variation_set_idx` (`variation_set_id`),
    KEY `consequence_type_idx` (`consequence_types`),
    KEY `source_idx` (`source_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=743963234 DEFAULT CHARSET=latin1;
  </code></pre>
  <p>It has over 700,000,000 records and occupies on the disk:</p>
  <pre><code># ls -lh variation_feature.*
  -rw-rw---- 1 mysql mysql 56K Mai  3 09:41 variation_feature.frm
  -rw-rw---- 1 mysql mysql 55G Mai  2 20:44 variation_feature.MYD
  -rw-rw---- 1 mysql mysql 61G Mai  2 22:27 variation_feature.MYI
  </code></pre>
  <p>Despite not getting any errors importing the variation_feature.txt some essential indexes are not working.
  In this case, selecting a known row of data based on variation_id won't return anything, e.g.</p>
  <pre><code>SELECT * 
  FROM variation_feature 
  WHERE variation_id = 617544728;
  
  --&gt; nothing
  </code></pre>
  <p>The value 617544728 seems not to be in the index, because</p>
  <pre><code>SELECT variation_id 
  FROM variation_feature 
  WHERE variation_id = 617544728;
  
  --&gt; nothing
  </code></pre>
  <p>Disabling the index and waiting for the long table scan returns the row:</p>
  <pre><code>ALTER TABLE variation_feature ALTER INDEX variation_idx IGNORED;
  
  SELECT * 
  FROM variation_feature 
  WHERE variation_id = 617544728;
  
  variation_feature_id  seq_region_id  seq_region_start  seq_region_end  seq_region_strand  variation_id  allele_string  ancestral_allele  variation_name  map_weight  flags   source_id  consequence_types  variation_set_id                                                                             class_attrib_id  somatic  minor_allele  minor_allele_freq  minor_allele_count  alignment_quality  evidence_attribs                 clinical_significance  display
  --------------------  -------------  ----------------  --------------  -----------------  ------------  -------------  ----------------  --------------  ----------  ------  ---------  -----------------  -------------------------------------------------------------------------------------------  ---------------  -------  ------------  -----------------  ------------------  -----------------  -------------------------------  ---------------------  -------
             632092737          27511         230845794       230845794                  1     617544728  A/G            G                 rs699                    1  &lt;null&gt;          1  missense_variant   2,5,6,9,10,11,12,13,15,16,17,23,24,25,26,30,40,42,43,44,45,47,48,49,50,51,52,53,54,55,56,57                2    false  A                        0.2949                1477             &lt;null&gt;  368,370,371,372,418,421,573,585  benign,risk factor           1
  </code></pre>
  <p>myisamchk is fixing the indexes without error, but the index &quot;variation_idx&quot; won't work.</p>
  <p>DROPping and re-CREATing the one index runs without error, but the index won't work.</p>
  <p>The other indexes are OK.</p>
  <p>In another genome of this database (ensembl homo_sapiens_variation_106_38 - slightly bigger) from here: <a href="ftp://ftp.ensembl.org/pub/release-106/mysql/homo_sapiens_variation_106_38/" rel="nofollow noreferrer">ftp://ftp.ensembl.org/pub/release-106/mysql/homo_sapiens_variation_106_38/</a> I have the same problem (on another computer but with the same program installations).
  With one difference: there is also the PRIMARY KEY (variation_feature_id) not working.</p>
  <p>myisamchk is also running without error, but to no avail.</p>
  <p>mysqlcheck (version 10.6 running very slow compared to 10.5) returns on the first computer then:</p>
  <pre><code>homo_sapiens_variation_106_37.variation_feature
  error    : Key in wrong position at page 22405134336
  error    : Corrupt
  </code></pre>
  <p>Now, this we know, but no repair tool can really repair or give a hint, what's wrong.</p>
  <p>I've CREATEd an index on variation_name: it's working.</p>
  <p>My changes to mariadb.cnf to adapt to the huge databases and the mysql versions of ensembl:</p>
  <pre><code>[mysqld]
  
  ## this was some time ago - because some bug mysql or mariadb didn't took this from the system
  default-time-zone = Europe/Berlin
  
  ## ensembl has mysql version 5.6
  ## because of table creation scripts I need compatibility:
  show_compatibility_56 = ON
  performance_schema
  
  ## ensembl writes in DATETIME fields: &quot;0000-00-00 00:00:00&quot;
  ## the new sql_mode in 5.7 doesn't allow it any more
  ## SHOW VARIABLES LIKE 'sql_mode' ;
  ## ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  ## so change sql_mode deleting NO_ZERO_IN_DATE,NO_ZERO_DATE
  sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  
  datadir = /mnt/SRVDATA/var/lib/mysql
  tmpdir  = /mnt/WORK/tmp
  
  [mariadb]
  
  [mariadb-10.6]
  
  ## MyISAM for building ensembl homo_sapiens
  lower_case_table_names=1
  
  bulk_insert_buffer_size = 1G
  myisam_sort_buffer_size = 56G
  sort_buffer_size = 56G
  </code></pre>
  <p>Thank you for the patience to read all this.</p>