I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such:
CREATE TABLE TICKETS (
TICKET_ID id PRIMARY KEY,
JOB_ID id,
ACTION_ID id,
STATUS str256 DEFAULT 'Pending'
);
I generally want to find the first ticket that hasn't been processed and is in Pending
status.
My processing loop would be:
- Retrieve 1st Ticket where
Pending
- Do work with Ticket.
- Update Ticket Status =>
Complete
- Repeat.
Nothing too fancy. If I'm watching the database while this loop runs I see the number of indexed reads climbs for each iteration. The performance doesn't seem to degrade terribly that I can tell, but the machine I'm testing on is pretty quick. However, I've received reports of performance degradation over time from some of my users.
I've got an index on Status
, but it still seems like it scans down the Ticket_Id
column each iteration. It seems like I'm overlooking something, but I'm not sure what. Is the climbing number of indexed reads for something like this expected, or is the index misbehaving in some way?
-- Edits for comments --
In Firebird you limit row retrieval like:
Select First 1
Job_ID, Ticket_Id
From
Tickets
Where
Status = 'Pending'
So when I say "first", I'm just asking it for a limited record set where Status = 'Pending'
.
ticket_id
, you probbaly need an index on(status, ticket_id)
ticket_id
actually performed worse than just having Status indexed.id
(the data type) a domain you defined?