How MySQL memory table saved the day

8 million records (at 7GB!) and it&#x27;s slow means there is something seriously wrong with your schema. That table would entirely fit in InnoDB Buffer Pool on any modern hardware.<p>I want to see your slow query log.

Just a heads up: <a href="http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html" rel="nofollow">http:&#x2F;&#x2F;dev.mysql.com&#x2F;doc&#x2F;refman&#x2F;5.1&#x2F;en&#x2F;memory-storage-engine...</a><p>The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.

Or you could actually use something designed for indexing data and searches, like Elasticsearch or Solr.<p>Either solution would have no problem indexing all their data, rather than having to limit it to a subset to fit in a in-memory table.

A better solution, is just to increase your innodb buffer size, you will get virtually same performance as the &#x27;memory&#x27; table once all the data is in memory. Plus all the data will be persisted.<p>This is an old, but still very useful script for helping to suggest what settings to tweak: <a href="https://github.com/major/MySQLTuner-perl" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;major&#x2F;MySQLTuner-perl</a><p><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html" rel="nofollow">http:&#x2F;&#x2F;dev.mysql.com&#x2F;doc&#x2F;refman&#x2F;5.5&#x2F;en&#x2F;innodb-buffer-pool.ht...</a>

I am always cautious of memory tables. They don&#x27;t support transactions, for example, and don&#x27;t work well multi user.<p>Really, the first stop is to use tokudb backend in mysql. If its still slow, and if you have a small subset that fits in ram, just put that straight into a hash table in app space.

We did something similar at work. We had to poll for changes in a table. So instead of polling the tables we added triggers to insert events to a MEMORY table and polled that table. It performs good enough for us.

&gt; Varchars take up the space for all the chars defined.<p>This only applies to memory tables. For non-memory tables, the size of varchar columns depends on the actual string size.<p>(edited. Thanks for correcting!)

What are some typical queries look like? Several minutes for searching 7M records doesn&#x27;t sound right. Are the columns indexed properly?

Haha. The Jon Snow meme got me in splits.