Cache of object Database Item List

Permalink June 10, 2011 at 1:48 PM

I have a class that extends DatabaseItemList where I am doing some basic filtering and pagination. However, it just so happens that the database I am working with is 2 million+ records large and, as you can image, my response times on the site are abysmal. I'm getting response times over 2 minutes in some cases.

I've looked into a couple of different options, but at the moment my hopes are to leverage caching. Is it possible to cache the object databaseItemList and still filter against it? Do any of you smart developers out there have another other tricks you use to search and filter large recordsets?

Just as an FYI -- the data can't be normalized any more than it currently is for reasons that are too annoying to go into, and we are running MySQL 4.1 so stored procedures of any kind are out.

My thanks.

jordanlev replied on Jun 10, 2011 at 3:20 pm Permalink Reply

I don't know what kind of caching is built into the database item list (I am guessing "not much" but I don't know for sure). But what you could do is make your own cache by running some kind of query that inserts your results into another database table (or another database entirely), then run the database item list query against that. Or look into a proper caching component like memcached or redis.

-Jordan