title: Rails app - populating the db cache and keeping it populated
body: <p>I have a Rails 5.2 app running on Ubuntu that works with a large MySQL database with over 50 million records.</p>
  <p>Users perform searches on the database. Specific searches are performed in an acceptable timeframe.</p>
  <p>However, the default is a complete wildcard search to return the first, say, 50 records, sorted (ordered) on one or more of the fields.</p>
  <p>The default wildcard search can take quite a while, but once performed, the results are cached, repeats of the wildcard search are very fast.</p>
  <p>Often the results remain cached, but sometimes the cache gets flushed, and the search has to be performed again which takes a long time again.  The cache has always been flushed in the morning, which I attribute to the server / database being backed up overnight, or the cloud server instance being somehow flushed.</p>
  <p>OK, I thought, I'll just run a cronjob that runs regularly (hourly) and re-populate the cache.  So that's what I do, and I am careful to actually perform the search (not just create an Active Record Query that is not actually performed).</p>
  <p>The cronjob invokes a method in the Model that performs the database query (and throws away the result).  I can see from the logging that the search is performed.</p>
  <p>Unfortunately, this attempt to populate the cache does not seem to benefit the HTTP RESTful API that pulls the same data. The first wildcard queries in a while always take a long time, even if a cronjob populate cache task has run recently.  However, again, once a RESTful API search has completed, subsequent repeats of that same query return the cached response very quickly.</p>
  <p>Maybe the RESTful query is not recognised as being the same search as performed in the cronjob?</p>
  <p>Suggestions as to how I can populate the cache, and keep it populated appreciated.</p>
  <p><strong>UPDATE</strong></p>
  <p>SQL generated by Rails for query,</p>
  <pre><code>SELECT  `products`.*
      FROM  `products`
      WHERE  `products`.`category` = 0
        AND  `products`.`available` = TRUE
      ORDER BY  LENGTH(prefix) ASC,
                LENGTH(numbers) ASC,
                LENGTH(suffix) ASC,
                `products`.`prefix` ASC,
                `products`.`numbers` ASC,
                `products`.`suffix` ASC
      LIMIT  20 OFFSET 0 
  </code></pre>
  <p>Relevant part of schema,</p>
  <pre><code>create_table &quot;products&quot;, force: :cascade do |t|
      t.integer &quot;category&quot;
      t.string &quot;prefix&quot;
      t.string &quot;numbers&quot;
      t.string &quot;suffix&quot;
      t.boolean &quot;available&quot;, default: false
      t.integer &quot;user_id&quot;
      t.integer &quot;price&quot;
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
      t.boolean &quot;open_sale&quot;, default: false
      t.index [&quot;category&quot;, &quot;prefix&quot;, &quot;numbers&quot;, &quot;suffix&quot;], name: &quot;index_products_category_prefix_numbers_suffix&quot;
    end
  </code></pre>