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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.