[AMQ-565] Add index to expiration to improve cleanup query

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0 M4
• Component/s: Message Store
• Assignee: Hiram R. Chirino
• Reporter: Hiram R. Chirino
• Created: 15/Feb/06 18:05
• Updated: 16/Feb/06 11:26
• Resolved: 16/Feb/06 11:26

Description

Per Paul Smith on the user@ mailing list:

....
Even with only a few thousand records in the table, the following:

create index idx_activemqmsgs_expiration on activemq_msgs(expiration)

appears to reduce the query plan cost of this scan by at least an order of magnitude, changing the scan from a Clustered Index Scan (that's just a table scan in SQL Server, if the table has a clustered index), to an index scan.