I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily handle this, however we use a lot of
GROUP BY clauses and pseudo tables to reduce our data to build our results.
It takes around 152 seconds to do this on the majority of results, but when we change one clause value, it eats up all of the space on the server and complains about some missing files.
EXPLAIN on our query, I found out that we were accessing over a trillion (yes...) rows. I've now reduced this to 655 billion. Our tables contain complex data.
I've been through our tables and really dug into the data to understand what field types we should be using, finding the maximum length of our columns and changing the structure to better fit. Also I've began making use of proper
SMALLINT field types.
Bearing in mind that it's extremely likely that these tables containing 1-2 million rows will soon jump to 2/3 times that amount, please help me answer these questions:
- Does setting a column to allow
NULLaffect the indexing?
- How & when should I create covering indexes? I was once told that data of similar values should be indexed into one index key. For example, dates, numbers etc. If not, what parameters should I use to define when to create a covering key?
- We run our server in a virtual machine. It's got 200GB of HDD space, 64GB of RAM. We only use InnoDB. We write millions of rows each day, then delete it out, and rewrite it. Once the data has been written we then need to join it up and build CSV files from it. What kind of
my.cnfsettings should we be using to create this balance of read/write? Here is our current configuration file contents.
Any and all help would be much appreciated!