Loading Data into Cache

[gotornot]

Hi All

I have a database with 1million products in and I'm finding no matter how i index it to be slow when getting results.

I have basically built a search engine that looks up the results.

Is their anyway of loading all this data which is less than 1gig into the ram (Cache) would that speed the searches up and then i could update it every 24 hours?

 

If the answer is yes.

 

Can someone point me in the right direction of how to achieve this using PHP.

 

Thanks

[Fadion]

With a good setup and some supporting hardware, 1m rows shouldn't be a big issue.

 

First try "EXPLAIN"ing the query/queries and check if they're using those indexes. Be sure to index the "SELECT"ed fields too, so MySQL uses the indexed fields only.

 

Next, on caching, there are several options. In my experience, APC does a very good job and is quite easy to install and use. It's primarily an opcode cacher, meaning that it will cache the compiled PHP script until it's modified, but offers setting and getting cache variables. I doubt the opcode cacher will give you any boost when the bottleneck is in the database, but what you can do is cache the result sets so they're not computed on each search.

 

Let me give a simple example:

<?php
$search = 'some search'; // the search keywords

if (apc_exists($search))
{
     // get the results from the cache
     $results = apc_fetch($search);
}
else
{
     // run the query and make the computations
     // results are passed to $results
     apc_store($search, $results);
}
?>

 

That's a very basic caching based on keywords, where one search is run once and served from memory on the next calls. However, you can build your own strategies, like caching popular searches and so on.

 

The other option would be using a pure caching solution like Memcached (which can be used as a distributed solution) and Redis. These are powerful key->value, shared, memory-based storage engines. Basically, they run as daemons and accept connections from any script, sharing data between them. Usage is pretty much the same as with APC.

 

As final words, keep in mind that caching depends completely on RAM and it gets filled up pretty fast. The amount you have wired in should be enough to run the PHP scripts, execute the database queries and have some space for caching too. It should be easy to find recommendations online (or from someone in this forum) for the hardware you need depending on the load, number of queries and how much you'll cache. In these days, with the cheap hardware costs for vps/dedicated, it's a lot easier to scale than to optimize. Especially if you don't have the experience for the later.