I’m hoping someone can help me here, currently, I’ve a large table
CREATE TABLE my_large_table ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, pname VARCHAR(512) NOT NULL, pvalue VARCHAR(64000) DEFAULT NULL, ptype VARCHAR(16) NOT NULL DEFAULT ‘unknown’, paccess VARCHAR(64000) DEFAULT NULL, anotherid BIGINT NOT NULL, INDEX idx_anotherid ( anotherid ))AUTO_INCREMENT = 1ENGINE = MyISAMDEFAULT CHARACTER SET utf8DEFAULT COLLATE utf8_bin;
and its just not using the index idx_anotherid when I do a query on anotherid
select * from my_large_table where anotherid = 2
will take FOREVER … it just hangs … doing an explain on it doesnt even show the index idx_anotherid as a possiblile index!
This query should return about 1000-3000 results
The table is approx 100million rows
I’ve tried using
‘use index(idx_anotherid)’ and ‘force index(idx_anotherid)’
with no luck
I had done a repair table quick … no luck
I’ve just set the max_seeks_for_key to 100 and am doing an optimize table on it (its taking forever) … will know the results of this in a few hours ughhh
Can anyone give any pointers?