> query_cache_size 0
> query_cache_type ON
ON, but no space?
Run this twice; how long does it take each time?
SELECT * FROM `accounts` WHERE ID=90000
The INDEX (PRIMARY KEY) on ID is a BTree. It is about 3 thousand blocks of 1KB each. (6417408 split across two indexes; 1KB is the MyISAM block size.) So, the BTree is about 2 layers deep. This means that, if nothing is cached, it would take 2 disk hits to find the index block with 90000 in it. In the index row is two things: the ID, and the offset into the data file. So, now, it does a random read into the data file (.MYD) to find the corresponding row. This is potentially another disk hit. For 3 disk hits, on a typical PC, I would expect 0.03 seconds.
Running the query a second time (virtually) guarantees that those 3 disk hits will be avoided because of caching in the key_buffer (for the index blocks) and the OS's cache (for the data block).
This guarantees that the query cache is not kicking in and delivering the resultset in virtually 0 time:
SELECT SQL_NO_CACHE ...
> 1 SIMPLE accounts const PRIMARY PRIMARY 4 const 1
This says that it will (probably) use the key.
Another way to "prove" whether it is doing the wrong thing... Do this before and after the query:
SHOW STATUS LIKE 'Handler%';
I would expect a couple of those numbers to increase by small amounts if the index is properly used. If not, then I would expect one of them to increase by 90000. Example (where `cities` has an index on `city`, but not on `population`):
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+-------+
15 rows in set (0.00 sec)
mysql> SELECT city, population FROM cities WHERE city = 'Bangalore';
+-----------+------------+
| city | population |
+-----------+------------+
| Bangalore | 4931603 |
+-----------+------------+
1 row in set (0.21 sec)
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 3 | <-- small count for index work
| Handler_read_next | 1 | <-- INDEX(`city`) was not UNIQUE, so check for another row
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+-------+
15 rows in set (0.00 sec)
mysql> SELECT city, population FROM cities WHERE population = 4931603 LIMIT 1;
+-----------+------------+
| city | population |
+-----------+------------+
| Bangalore | 4931603 |
+-----------+------------+
1 row in set (4.62 sec)
mysql> SHOW STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 2 | <-- InnoDB, and autocommit=1
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 5 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 678983 | <-- That indicates a scan
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+--------+
For better efficiency, change
key_buffer_size 16777216
to 300M. Don't bother making it any bigger than the total of all Index_length in SHOW TABLE STATUS. I see 6.4M in that one table.
To see how long it takes to traverse the whole table, do (for example):
SELECT * FROM accounts WHERE name = 'xx';