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

[Andy Deakin]

Apologies, one of my test tables has a different column name. RemoteID and SensorID are the same column.

After my initial frustration with the large InnoDB file that would not get smaller, I added innodb_file_per_table to my.cnf, but have not tested since changing it.

The server has 4GB memory.

Here is my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 64M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
table_cache            = 256
query_cache_limit       = 8M
query_cache_size        = 16M
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_buffer_pool_size=256M
innodb_file_per_table
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer              = 16M