Transfer table to Memcache

Question

Hi, I have a large table and I'd like to store the results in Memcache. I have Memcache set up on my server but there doesn't seem to be any useful documentation (that I can find) on how to efficiently transfer large amounts of data. The only way that I currently can think of is to write a mysql query that grabs the key and value of the table and then saves that in Memcache. Its not a particularly scalable solution (especially when my query generates a few hundred thousand rows). Any advice on how to do this?

EDIT: there is some confusion about what I"m attempting to do. Lets say that I have a table with two fields (key and value). I am pulling in information on the fly and have to match it to the key and return the value. I'd like to avoid having to execute ~1000 queries per page load. Memcache seems like a perfect alternative because its set up to use key value. Lets say this table has 100K rows. THe only way that I know to get that data from the db table to memcache is to run a query that loops through every row in the table and creates an individual memcache row.

Questions: Is this a good way to use memcache? If yes, is there a better way to transfer my table?

Answer 1

you can actually pull all the rows in an array and store the array in memcache

memcache_set($memcache_obj, 'var_key', $your_array);

but you have to remember few things

• PHP will serialize/unserialize the array from memcache so if you have many rows it might be slower then actually querying the DB
• you cannot do any filtering (NO SQL), if you want to filter some items you have to implement this filter yourself and it would probably perform worst then the DB engine.
• memcache won't store more then 1 megabyte ...

I don't know what you try to achieve but the general use of memcache is:

• store the result of SQL/time consuming processing but the number of resulting row should be small
• store some pre created (X)HTML blobs to avoid DB access.
• user session storage

Answer 2

Russ,

It sounds almost as if using a MySQL table with the storage engine set to MEMORY might be your way to go.

A RAM based table gives you the flexibility of using SQL, and also prevents disk thrashing due to a large amount of reads/writes (like memcached).

However, a RAM based table is very volatile. If anything is stored in the table and not flushed to a disk based table, and you lose power... well, you just lost your data. That being said, ensure you flush to a real disk-based table every once in a while.

Also, another plus from using memory tables is you can store all the typical MySQL data types, so there is no 1MB size limit.