Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
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 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 at 11:28

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.