We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space.
We quite often have to query this table - sometimes as far back as a few years, so we can't archive much of it - and because it takes a long time, this often creates a bottleneck for other queries which I believe is caused by the hard disk's physical speed.
The machine it's on it is quite old and slow and we're looking at moving over to a new one, but this could take a while so we're looking for an interim solution.
The machine has multiple physical drives. If we turned on innodb-file-per-table and moved the large table to a different physical drive, could we expect a performance increase when querying this table?