I would like to optimize my MySQL DB. I noticed some important points as follows:

  1. Enabling Query cache.
  2. Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I need not worry about this facility's drawback. Before executing a query, I'll check if memory tables exist or not.)
  3. Increasing key_buffer_size.

My question is, can I use both Query cache and memory storage? As well as what is the role of key_buffer_size?

Why am I asking? Because everything depends on system memory. How these 3 options will work together? Can I do these things together?

  • This is only a guess, but I'd say people are not responding to this question because they cannot understand what you are asking. At least, that is what is happening to me...
    – Hannah Vernon
    Nov 23 '12 at 2:07
  • 2
    Please check the other copy of this same question and turn the language of this one into something human-readable. I bet you got downvotes on that one because of all the contractions. This is not Twitter, you can post questions literally of thousands of characters.
    – dezso
    Nov 23 '12 at 9:10

Query cache can be combined with memory storage but it'll only make sense if don't have many updates (as your cached results will be no longer actual after your change your data).

Basically query cache stores results of your SELECT queries (if run again, data is retrieved faster), key buffer size stores indices (helps to use them more efficiently, i.e. to retrieve data quicker) and you said you know what memory storage is.

All three options you mentioned can be used together, but the real efficiency depends on your usage pattern. But hey won't do any harm when combined (by that I mean assigning bigger values to cache and buffer while using memory storage) as long as you have enough memory.

P.S. As you probably reckon yourself, your setup screams you're using a wrong tool. I can imagine situation where it is still necessary, e.g. using legacy / unchangeable code that depends on MySQL but otherwise consider redesigning and using other data storages besides MySQL, permanent or temporary like memcache.

  • I am using latest extension such as mysqli and utilizing all features .. problem is day by day db size increase as well as traffic also, so that I asked this. Nov 22 '12 at 17:11
  • Consider splitting your load between several databases / db servers. Surely that would require to change your code but it'll pay off when you face even more traffic, you'll be able to scale much easier.
    – Yuriy
    Nov 22 '12 at 22:29
  • Also, what is your problem so far? Are some queries much slower than others blocking the necessary tables, or are they all reasonably fast, but there are just too many of them? Quite different scenarios with different solutions.
    – Yuriy
    Nov 22 '12 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.