Re: Keys ignored for larger tables!?

[tamas marki]

Hi,

here's the data:

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+------------+
| Variable_name                 | Value      |
+-------------------------------+------------+
| bulk_insert_buffer_size       | 8388608    | 
| innodb_buffer_pool_awe_mem_mb | 0          | 
| innodb_buffer_pool_size       | 1572864000 | 
| innodb_log_buffer_size        | 1048576    | 
| join_buffer_size              | 131072     | 
| key_buffer_size               | 16777216   | 
| myisam_sort_buffer_size       | 8388608    | 
| net_buffer_length             | 16384      | 
| preload_buffer_size           | 32768      | 
| read_buffer_size              | 131072     | 
| read_rnd_buffer_size          | 262144     | 
| sort_buffer_size              | 2097144    | 
+-------------------------------+------------+
12 rows in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  | 
| query_cache_min_res_unit     | 4096     | 
| query_cache_size             | 16777216 | 
| query_cache_type             | ON       | 
| query_cache_wlock_invalidate | OFF      | 
+------------------------------+----------+
5 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1743     | 
| Qcache_free_memory      | 6028912  | 
| Qcache_hits             | 32825442 | 
| Qcache_inserts          | 13835582 | 
| Qcache_lowmem_prunes    | 3684899  | 
| Qcache_not_cached       | 3857966  | 
| Qcache_queries_in_cache | 6073     | 
| Qcache_total_blocks     | 14433    | 
+-------------------------+----------+
8 rows in set (0.02 sec)

Changing to NOT NULL seems to have improved performance a bit (about 30% faster), but I think there is still room for improvement...

Thanks for your help!