indexing - MySQL returns zero rows(Empty set) without limit because of broken/outdated index -

i work on large table around 1.5k entries,

create table `crawler` ( `id` int(11) not null auto_increment, ... `provider_id` int(11) default null, primary key (`id`), key `crawler_provider_id` (`provider_id`), ... ) engine=myisam ... 

provider_id used link table table 'providers, cleared , repopulated new data. trying recreate connections 'crawler' 'providers' (which should not matter in case of myisam), reason in script mysql returns 0 rows if don't provide limit.

mysql> select `crawler`.`id` `crawler` `crawler`.`provider_id` > 1371; empty set (0.40 sec) 

but

mysql> select count(*) `crawler` `crawler`.`provider_id` > 1371; |   346999 | 

and

mysql> select `crawler`.`id` `crawler` `crawler`.`provider_id` > 1371 limit 10; 10 rows in set (0.01 sec) 

if select data table , check myself can see values greater 1371.

i able fix deleting indexes (and recreating later), extremely confused. i've never seen indexes going out of sync table data (and unaware can affect values of returned rows). unfortunately haven't performed "check table" before deleting indexes, has "status=ok" right now, can't see wrong in logs, , "repair table" shows no problems.

so, common problem? can reason? server had low ram problems before, issue here well?

your query related table corruption in myisam.

i did

root@localhost [kris]> create table crawler (   id integer not null auto_increment primary key,    provider_id int(11) default null,    primary key (id),    key crawler_provider_id (provider_id) ) engine = myisam; root@localhost [kris]> insert crawler ( id, provider_id ) values ( null, 1 );</code> 

and repeated

root@localhost [kris]> insert crawler ( id, provider_id)    select null, rand() * 120000 crawler; 

until had

root@localhost [kris]> select count(*) crawler; +----------+ | count(*) | +----------+ |   524288 | +----------+ 1 row in set (0.00 sec) 

i have

root@localhost [kris]> select count(*) `crawler` `crawler`.`provider_id` > 1371; +----------+ | count(*) | +----------+ |   518389 | +----------+ 1 row in set (0.27 sec) 

which comparable in size gave in example above. 2 different plans query , without limit clause.

without limit clause full table scan (all) not using index:

root@localhost [kris]> explain select `crawler`.`id` `crawler` `crawler`.`provider_id` > 1371\g *************************** 1. row ***************************            id: 1   select_type: simple         table: crawler          type: possible_keys: crawler_provider_id           key: null       key_len: null           ref: null          rows: 524288         extra: using 1 row in set (0.00 sec) 

with limit clause, index used range access

root@localhost [kris]> explain select `crawler`.`id` `crawler` `crawler`.`provider_id` > 1371 limit 10\g *************************** 1. row ***************************            id: 1   select_type: simple         table: crawler          type: range possible_keys: crawler_provider_id           key: crawler_provider_id       key_len: 5           ref: null          rows: 518136         extra: using 1 row in set (0.00 sec) 

in example, without limit clause (full table scan) no data, limit clause (range access using index) data. points corrupted myd file.

alter table, repair table or optimize table, copy data , kept indexes source table hidden new version of table in new format. when completed, hidden new table replace old version of table (which renamed hidden name, , dropped).

that is, dropping indexes repaired table.