I'm running queries on a large database (tens of millions of objects), and I'm running into a performance problem using two indexes. An entity I'm querying has two indexed fields - one is the primary key (id) and so is different for each object, and the other is an object reference that only has a few tens of different values. When I query by just the primary key, it's (of course) extremely fast, but when I also query using the other key, queries can take minutes. Looking at the database log, the way the query is run is that all values of the object reference key are scanned to get candidates for querying the primary key. This is the slow part.
I'm looking for a way to optimise this querying. What I'm trying to fetch in my queries are a limited number of the most recent objects, like this:
select from Thing where reference == :objectReference order by id desc
With the query limited to (say) 10 results.
Is there any obvious way to speed up this querying? Taking minutes is not acceptable.