I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance.

We've got an InnoDB table which is currently storing ~3.5 million rows, which I know isn't a lot in comparison to some, but it's big enough for me to start caring about some things. Specifically:

  • I run a query daily which selects from a smaller table, joining this large on and another. It's slow, taking 10s of minutes to complete.
  • I write to it a lot, to speed it up, I write all of my data to a CSV file and run LOAD IN LOCAL FILE which is much quicker than a TRANSACTION or writing each line at a time.
  • The data is accessed a lot, although we cache the results from it daily, the first hit on it can take a while.

Testing with it is a pain, because it takes so long.

What can I do to speed it up? I've indexed the appropriate columns, date fields are indexed under the same key etc. I've read about partitions but I'm not sure on which field I should do this by?

My table structure is built around a calendar, so I'm thinking months or my "nights" column which is only ever 7, 10, 11 or 14.

Any help on this would be much appreciated.

  • If your most frequent queries already use appropriate indexes, partitioning the table shouldn't actually change much in terms of performance. If you're scanning a lot, then yes that could help, but how you partition depends on what the queries search by (and trying to eliminate the scans would be the first thing to investigate). – Mat Mar 25 '13 at 10:59
  • @Mat, okay, so I'd use partitions if I'm scanning, which I am doing, but I'm also writing. When using partitions do I need to always re-partition after adding more data? – James Mar 25 '13 at 11:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.