CODEWITHSUNDEEP
mysql
Andrei Ciobanu
Andrei Ciobanu

Reputation: 12858

Using memory tables for static sets of values

I don't have lots of experience in designing database schemas, and certainly not in MySQL.

So my question is: "If I have static sets of data, like countries, cities, gender - male / female / undecided :P, etc. is it going to be an advantage for me if I create this relatively small tables in memory ?"

Upvotes: 0

Views: 197

Answers (4)

Chuan Ma
Chuan Ma

Reputation: 9914

No. Create a normal table and use SQL_CACHE to improve performance.

SELECT SQL_CACHE id, name FROM customer;

The result will be cached in memory for better performance, but the data is still persistent in hard drive.

Reference: http://dev.mysql.com/doc/refman/4.1/en/query-cache-in-select.html

UPDATE [ Add more info ]

reference link

In my.cnf, there are 3 possible values you can set for query cache type:

query_cache_type = 0 --> query cache is totally off. MySQL won't cache anything at all.
query_cache_type = 1 --> query cache is on by default. you have to use SQL_NO_CACHE hint to disallow caching some queries (e.g., highly dynamic queries)
query_cache_type = 2 --> query cache is off by default. you have to use SQL_CACHE hint to allow caching.

By default, that value is 1 (on).

However, your memory will be limited. So you'd prefer to tell mysql which query you want to cache, and which you don't. For example, you want to cache country list, but you may not want to cache profile query if you've already had memcache for that. Using cache hints makes a lot of sense here.

Upvotes: 1

Steve
Steve

Reputation: 3703

This is a good question, but it isn't an easy one to answer as it truly depends on individual use cases. In most cases the answer is no.

The MEMORY engine is a little different than normal storage, as it's name suggests it stores the data in memory rather than on disk – it uses a hashed based indexing system by default which is very fast (although you can also use the binary tree index too).

The MEMORY engine is great for creating temporary tables but when the server shutsdown the data is lost. This will create extra work in your application logic to check if it exists and has data and to populate it if not.

A far better approach would be to use a normal database storage type and have a good index so it is quick, maybe even look at caching if used a lot.

There are some important restrictions on MEMORY tables:

  • The MEMORY table uses a fixed row size, so if you’re using variable length data types such as VARCHAR it will actually be fixed and use the WHOLE space possible for each row.
  • The MEMORY table cannot contain TEXT / BLOB types
  • There is no concept of garbage collection in order to reclaim memory. If you delete individual rows from a MEMORY table the memory previously used isn’t returned to the OS until the whole table is deleted. You can force this with an ALTER TABLE command but generally its worth thinking about if you’re going to keep MEMORY tables around for a while.

Upvotes: 1

thinice
thinice

Reputation: 710

Long story short: No. Because that smallish amount of read-mostly data is going to be cached or placed into a buffer based on it's frequency of access.

Memory tables are emptied on restart of the MySQL service as well, so you'll have to populate them at least once per startup of the service.

MySQL memory tables are mostly useful when you REALLY want to avoid IO because you have a very high amount of arithmetic or IO centered around a particular table and can stand to lose the data if the server crashes or restarts. MEMORY tables of limited use due to being cleared out when the service restarts.

An InnoDB table would be the safest pick.

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71422

It won't help you to create those tables in memory if you need to persist the data across server shutdowns. Memory tables should only be used to store temporary data. There is nothing to say however that you couldn't store the base data in disk-based tables and use some process to load the data into memory tables for faster access.

This seems like a lot of work to me however, since, if:

  • you use properly indexed disk-based tables

  • you allocate enough memory for the entire indexes to be resident in memory

  • you design you queries to always properly utilize your indexes

  • you utilize MySQL's query cache

You are not likely to see that significant of a performance gain for tables of relatively small size which these would likely be.

Upvotes: 1

Related Questions