Re: Almost whole table in single index: is there a better way?

[Aftab Khan]

>I was put off InnoDB by its much larger footprint, and assumptions that if the index is larger, disk seeks would be slower, and the index is less likely to fit in memory. I will try converting a table to InnoDB, and see how it performs.
>InnoDB
You aren't using any secondary index(es), you search data using PK and row data is on the same page where the index search leads.

>This is an excellent idea. Is it possible to specify this on a per-table basis by specifying MAX_ROWS, rather than changing the global my.cnf?
yes, you can use MAX_ROWS when creating MYISAM table. You may also try PACK_KEYS=1.


With MyISAM, do
key_buffer_size = 512M
skip-innodb # disable innodb, if you are using myisam only

With InnoDB, do
key_buffer_size = 20M
innodb_buffer_pool_size = 2G
innodb_file_per_table = 1
innodb_log_file_size =64M



Edited 1 time(s). Last edit at 08/28/2012 10:54AM by Aftab Khan.