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?

  • A database of less than a gigabyte is a tiny database. How much RAM do you have in this machine? – Colin 't Hart Jan 26 '15 at 9:43
  • The server has 4GiB – Prinsig Jan 26 '15 at 9:48
  • How is MySQL configured to use the memory? See stackoverflow.com/questions/1178736/mysql-maximum-memory-usage – Colin 't Hart Jan 26 '15 at 10:11
  • No, moving to another drive is probably not the 'solution'. Instead, let's look at the queries and indexes and see what can be done to speed up your current & past queries. Also, we can look at PARTITIONing. Please provide SHOW CREATE TABLE and EXPLAIN SELECT... – Rick James Feb 7 '15 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.