MEMORY Storage Engine

[imperium2335]

In what situations would it be good to use MEMORY?

 

I have just bought a new DB server for us and it has 24gb ram.

 

Is one potential use to load into a memory table all information the user may (or may not) want to use or see upon logging into their user area? Then any changes they make get saved to the hard version of the memory data and the system is checking every second or so for changes to the hard copy and makes updates to the memory table?

[QuickOldCar]

Although you may have adequate memory, it's never a good thing to run out of memory.

 

You can try upping the query cache, also the key cache.

http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

http://dev.mysql.com/doc/refman/5.6/en/myisam-key-cache.html

Make indexes in mysql on any WHERE,AND,OR statements in queries.

http://dev.mysql.com/doc/refman/5.6/en/create-index.html

Page caching will eliminate many calls to the database and display pages faster.

can look into this:

http://memcached.org/

or see here:

http://php.net/manual/en/book.memcache.php

 

[imperium2335]

Thanks,

 

I just added a few indexes on foreign keys and it has speeded things up significantly! so thanks.

 

Does this apply to any column that I use in a WHERE like you say, in all cases? Or are there some instances where it's not a good idea?

[QuickOldCar]

There is more than one type of indexing, and also multiple indexing.

 

Pretty much any WHERE,AND,OR helps greatly, you can even improve on that doing specific groups of keys.

http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

 

Using explain can help you determine what you need.

explain select * from table where a=1 or b=1;

 

http://dev.mysql.com/doc/refman/5.6/en/using-explain.html