# Large table ignoring index? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 25, 2010, 6:34am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483 "2010-08-25T06:34:36Z")\ **Posts on this page:** 7\ **Page:** 1

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 25, 2010, 6:34am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/1 "2010-08-25T06:34:36Z")

Hey, 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 a simple 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? Thanks /Bal ---

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 25, 2010, 7:57am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/2 "2010-08-25T07:57:06Z")

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 25, 2010, 8:37am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/3 "2010-08-25T08:37:34Z")

Trying to drop the index and recreate it now 😕 Will update ya in a few hours ( ---

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 25, 2010, 8:41am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/4 "2010-08-25T08:41:33Z")

mysql\> show variables;±----------------------------------------±------------------------------------------------------------------------------------------+| Variable\_name | Value |±----------------------------------------±------------------------------------------------------------------------------------------+| auto\_increment\_increment | 1 || auto\_increment\_offset | 1 || autocommit | ON || automatic\_sp\_privileges | ON || back\_log | 128 || basedir | / || big\_tables | OFF || binlog\_cache\_size | 32768 || binlog\_direct\_non\_transactional\_updates | OFF || binlog\_format | STATEMENT || bulk\_insert\_buffer\_size | 8388608 || character\_set\_client | latin1 || character\_set\_connection | latin1 || character\_set\_database | utf8 || character\_set\_filesystem | binary || character\_set\_results | latin1 || character\_set\_server | latin1 || character\_set\_system | utf8 || character\_sets\_dir | /usr/share/mysql/charsets/ || collation\_connection | latin1\_swedish\_ci || collation\_database | utf8\_general\_ci || collation\_server | latin1\_swedish\_ci || completion\_type | 0 || concurrent\_insert | 1 || connect\_timeout | 120 || datadir | /data/mysql/ || date\_format | %Y-%m-%d || datetime\_format | %Y-%m-%d %H:%i:%s || default\_week\_format | 0 || delay\_key\_write | ON || delayed\_insert\_limit | 100 || delayed\_insert\_timeout | 300 || delayed\_queue\_size | 1000 || div\_precision\_increment | 4 || engine\_condition\_pushdown | ON || error\_count | 0 || event\_scheduler | OFF || expire\_logs\_days | 5 || flush | OFF || flush\_time | 0 || foreign\_key\_checks | ON || ft\_boolean\_syntax | + -\>\<()~\*:“”&| || ft\_max\_word\_len | 84 || ft\_min\_word\_len | 4 || ft\_query\_expansion\_limit | 20 || ft\_stopword\_file | (built-in) || general\_log | OFF || general\_log\_file | /data/mysql/venus.log || group\_concat\_max\_len | 1024 || have\_community\_features | YES || have\_compress | YES || have\_crypt | YES || have\_csv | YES || have\_dynamic\_loading | YES || have\_geometry | YES || have\_innodb | DISABLED || have\_ndbcluster | NO || have\_openssl | DISABLED || have\_partitioning | YES || have\_query\_cache | YES || have\_rtree\_keys | YES || have\_ssl | DISABLED || have\_symlink | YES || hostname | venus || identity | 0 || ignore\_builtin\_innodb | OFF || init\_connect | SET AUTOCOMMIT=1 || init\_file | || init\_slave | || insert\_id | 0 || interactive\_timeout | 360 || join\_buffer\_size | 131072 || keep\_files\_on\_create | OFF || key\_buffer\_size | 1073741824 || key\_cache\_age\_threshold | 300 || key\_cache\_block\_size | 1024 || key\_cache\_division\_limit | 100 || language | /usr/share/mysql/english/ || large\_files\_support | ON || large\_page\_size | 0 || large\_pages | OFF || last\_insert\_id | 0 || lc\_time\_names | en\_US || license | GPL || local\_infile | ON || locked\_in\_memory | OFF || log | OFF || log\_bin | OFF || log\_bin\_trust\_function\_creators | OFF || log\_bin\_trust\_routine\_creators | OFF || log\_error | /var/log/mysql/mysql-err.log || log\_output | FILE || log\_queries\_not\_using\_indexes | OFF || log\_slave\_updates | OFF || log\_slow\_queries | ON || log\_warnings | 1 || long\_query\_time | 3.000000 || low\_priority\_updates | OFF || lower\_case\_file\_system | OFF || lower\_case\_table\_names | 0 || max\_allowed\_packet | 5242880 || max\_binlog\_cache\_size | 4294963200 || max\_binlog\_size | 1073741824 || max\_connect\_errors | 65535 || max\_connections | 10000 || max\_delayed\_threads | 20 || max\_error\_count | 64 || max\_heap\_table\_size | 16777216 || max\_insert\_delayed\_threads | 20 || max\_join\_size | 18446744073709551615 || max\_length\_for\_sort\_data | 1024 || max\_prepared\_stmt\_count | 16382 || max\_relay\_log\_size | 0 || max\_seeks\_for\_key | 100 || max\_sort\_length | 1024 || max\_sp\_recursion\_depth | 0 || max\_tmp\_tables | 32 || max\_user\_connections | 0 || max\_write\_lock\_count | 4294967295 || min\_examined\_row\_limit | 0 || multi\_range\_count | 256 || myisam\_data\_pointer\_size | 6 || myisam\_max\_sort\_file\_size | 1073741824 || myisam\_mmap\_size | 4294967295 || myisam\_recover\_options | OFF || myisam\_repair\_threads | 3 || myisam\_sort\_buffer\_size | 134217728 || myisam\_stats\_method | nulls\_unequal || myisam\_use\_mmap | OFF || net\_buffer\_length | 16384 || net\_read\_timeout | 120 || net\_retry\_count | 60 || net\_write\_timeout | 120 || new | OFF || old | OFF || old\_alter\_table | OFF || old\_passwords | OFF || open\_files\_limit | 1024 || optimizer\_prune\_level | 1 || optimizer\_search\_depth | 62 || optimizer\_switch | index\_merge=on,index\_merge\_union=on,index\_merge\_sort\_union=on,index\_merge\_intersection=on || pid\_file | /data/mysql/venus.pid || plugin\_dir | /usr/lib/mysql/plugin || port | 3306 || preload\_buffer\_size | 32768 || profiling | OFF || profiling\_history\_size | 15 || protocol\_version | 10 || pseudo\_thread\_id | 74 || query\_alloc\_block\_size | 8192 || query\_cache\_limit | 1048576 || query\_cache\_min\_res\_unit | 4096 || query\_cache\_size | 33554432 || query\_cache\_type | ON || query\_cache\_wlock\_invalidate | OFF || query\_prealloc\_size | 8192 || rand\_seed1 | || rand\_seed2 | || range\_alloc\_block\_size | 4096 || read\_buffer\_size | 786432 || read\_only | OFF || read\_rnd\_buffer\_size | 786432 || relay\_log | || relay\_log\_index | || relay\_log\_info\_file | relay-log.info || relay\_log\_purge | ON || relay\_log\_space\_limit | 0 || report\_host | || report\_password | || report\_port | 3306 || report\_user | || rpl\_recovery\_rank | 0 || secure\_auth | OFF || secure\_file\_priv | || server\_id | 0 || skip\_external\_locking | ON || skip\_name\_resolve | OFF || skip\_networking | OFF || skip\_show\_database | OFF || slave\_compressed\_protocol | OFF || slave\_exec\_mode | STRICT || slave\_load\_tmpdir | /var/tmp/ || slave\_net\_timeout | 3600 || slave\_skip\_errors | OFF || slave\_transaction\_retries | 10 || slow\_launch\_time | 2 || slow\_query\_log | ON || slow\_query\_log\_file | /var/log/mysql/mysql-slow.log || socket | /var/lib/mysql/mysql.sock || sort\_buffer\_size | 1048576 || sql\_auto\_is\_null | ON || sql\_big\_selects | ON || sql\_big\_tables | OFF || sql\_buffer\_result | OFF || sql\_log\_bin | ON || sql\_log\_off | OFF || sql\_log\_update | ON || sql\_low\_priority\_updates | OFF || sql\_max\_join\_size | 18446744073709551615 || sql\_mode | || sql\_notes | ON || sql\_quote\_show\_create | ON || sql\_safe\_updates | OFF || sql\_select\_limit | 18446744073709551615 || sql\_slave\_skip\_counter | || sql\_warnings | OFF || ssl\_ca | || ssl\_capath | || ssl\_cert | || ssl\_cipher | || ssl\_key | || storage\_engine | MyISAM || sync\_binlog | 0 || sync\_frm | ON || system\_time\_zone | IST || table\_definition\_cache | 256 || table\_lock\_wait\_timeout | 60 || table\_open\_cache | 20000 || table\_type | MyISAM || thread\_cache\_size | 15 || thread\_handling | one-thread-per-connection || thread\_stack | 262144 || time\_format | %H:%i:%s || time\_zone | SYSTEM || timed\_mutexes | OFF || timestamp | 1282743822 || tmp\_table\_size | 16777216 || tmpdir | /var/tmp/ || transaction\_alloc\_block\_size | 8192 || transaction\_prealloc\_size | 4096 || tx\_isolation | REPEATABLE-READ || unique\_checks | ON || updatable\_views\_with\_limit | YES || version | 5.1.47-community-log || version\_comment | MySQL Community Server (GPL) || version\_compile\_machine | i686 || version\_compile\_os | pc-linux-gnu || wait\_timeout | 360 || warning\_count | 0 |±----------------------------------------±------------------------------------------------------------------------------------------+239 rows in set (0.00 sec)mysql\> ---

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 25, 2010, 10:56am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/5 "2010-08-25T10:56:06Z")

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\ **Post date:** [August 26, 2010, 11:54am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/6 "2010-08-26T11:54:30Z")

This looks like a bug to me. You could file a bug report, but you’re probably going to have a hard time reproducing it now, so the bug report will be useless. PS: Did you intentionally disable InnoDB? ---

**Author:** ![balmark](https://avatars.discourse-cdn.com/v4/letter/b/779978/32.png) [@balmark](https://forums.percona.com/u/balmark)\ **Post date:** [August 31, 2010, 3:28am UTC](https://forums.percona.com/t/large-table-ignoring-index/1483/7 "2010-08-31T03:28:45Z")

We disable innodb, apparently its recommended if you’re not using it due to resources it can take up In the infinate wisdom of some guys here, we’re using myiasm instead of innodb… even with my recommendations to move due to it being a high rate insert/update app… Gotta love table locking. In the same infinate wisdom, instead of using innodb, the huge table is being split into thousands and thousands of smaller tables … mytable\_ x 150,000 What a goddamn joke. Some guy here read somewhere that its common practice and said google and the like use it as a strategy so everyone just ooooohhhhh’d and ignored me saying Might create a thread just asking what people think of splitting a large table into thousands upon thousands of small tables.