Mysql – Cache all thesql table

cacheMySQL

Maybe it sound's like i'm an idiot… But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached… But could i send all my table data to cache via some query, to improve speed, so that simple my table will be in ram memory?
I read a lot about encreasing cahce size etc… But how can i send this all data in cache not when user do some query (if so user see data from cache)…

*************************** 1. row ***************************
       Table: ART_LOOKUP
Create Table: CREATE TABLE `ART_LOOKUP` (
  `ARL_ART_ID` int(11) NOT NULL,
  `ARL_SEARCH_NUMBER` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_KIND` int(3) DEFAULT NULL,
  `ARL_BRA_ID` int(11) DEFAULT NULL,
  `ARL_DISPLAY_NR` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ARL_DISPLAY` int(11) DEFAULT NULL,
  `ARL_BLOCK` int(11) DEFAULT NULL,
  `ARL_SORT` int(11) DEFAULT NULL,
  KEY `ARL_ART_ID` (`ARL_ART_ID`),
  FULLTEXT KEY `ARL_SEARCH_NUMBER` (`ARL_SEARCH_NUMBER`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)



*************************** 1. row ***************************
       Table: ARTICLES
Create Table: CREATE TABLE `ARTICLES` (
  `ART_ID` int(11) NOT NULL,
  `ART_ARTICLE_NR` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `ART_SUP_ID` int(11) DEFAULT NULL,
  `ART_DES_ID` int(11) DEFAULT NULL,
  `ART_COMPLETE_DES_ID` int(11) DEFAULT NULL,
  `ART_CTM` blob,
  `ART_PACK_SELFSERVICE` int(11) DEFAULT NULL,
  `ART_MATERIAL_MARK` int(11) DEFAULT NULL,
  `ART_REPLACEMENT` int(11) DEFAULT NULL,
  `ART_ACCESSORY` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE1` int(11) DEFAULT NULL,
  `ART_BATCH_SIZE2` int(11) DEFAULT NULL,
  `Quantity` int(5) DEFAULT NULL,
  `Price` float DEFAULT NULL,
  `Waittime` int(3) NOT NULL,
  `datetime_of_update` datetime DEFAULT NULL,
  KEY `ART_ID` (`ART_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

via privacy can't show all

Best Answer

Certain database engines do support pinning objects to cache, Oracle and MS-SQL to name a couple. MySQL does not have this option, as far as I know.

On the other hand I cannot think of many use cases for this option, as modern database systems are very good at managing cache. MySQL offers block caching with an LRU algorithm through the InnoDB storage engine.

There is also an in memory storage engine, but there is no easy and efficient way replicate changes back to disk.

There are complete in-memory databases out there if you want everything in RAM, including MySQL Cluster.

On a sidenote, in practical use there is a trade-off between I/O and CPU resources. More cache means less I/O but more CPU is consumed, and there is a real possibility of introducing new bottlenecks.

Related Question