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.

After using 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 INT, TINYINT and 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:

  1. Does setting a column to allow NULL affect the indexing?
  2. 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?
  3. 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.cnf settings 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!

  • 4
    Use them to the MAX()! – Kermit Nov 4 '13 at 16:21
  • I'm upvoting that, simply because it made me chuckle after this very long day. Thanks! – James Nov 4 '13 at 16:23
  • If I could only get rep for it. – Kermit Nov 4 '13 at 16:33
  • Is it one query that (usually) takes 152 seconds? Or several smaller ones? – ypercubeᵀᴹ Nov 4 '13 at 16:55
  • 2
    When you said you tried optimising your queries, did you mean you tried only fiddling with DB/server settings, table indices etc. or did you also try rewriting the queries themselves? You aren't offering us to take a look at them, I guess you must reckon you've optimised them as much as could be, or do you think there could still be room for more optimisation? – Andriy M Nov 4 '13 at 17:41

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.