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 FILEwhich is much quicker than a
TRANSACTIONor 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.