I'm having trouble understanding why different queries can perform so differently, like <1 sec. versus 20+ sec. In this case, I have a date field with a regular index on it. When I select from the table "where Date >= '-1 month'" the query performs very fast. When I select "where Date between $x and $y", it performs very poorly. I'd like the user to be able to select the desired date range, but the wait is killing me. Do I need some other kind of index for the between clause to work effectively? The data is already sorted by date in ascending order.
OK, great, that works well. Now I have another twist on the problem that gets back to the other part of your first question -- "are there any other parts of my where clause?". The full query involves two other criteria besides the date range. This database is essentially an access log that records three things: access time (AccessDate date), IP address of remote host (RemoteAddr varchar), and a resource ID (dbcn varchar). There is a metmorph index on dbcn, a normal index on RemoteAddr, and a compound index on (AccessDate, RemoteAddr). I want to produce reports on a selected date range, an optional (and possibly multi-valued) selected resource, and an IP range. So my full query looks like:
select count(*) from dbusage where dbcn like $dbcn and AccessDate between $startdate and $enddate and RemoteAddr matches $IPrange
I tried creating a metamorph compound index on (dbcn, AccessDate, RemoteAddr) but that doesn't seem to help performance.