Add an index on provider, id columns for improved performance.

CommentFileSizeAuthor
#4 add_id_index-1349744-4.patch712 bytesdbehrman
#1 add_provider_id_index-1349744-1-D6.patch711 bytespdrake

Comments

pdrake’s picture

pdrake commented
StatusFileSize
new add_provider_id_index-1349744-1-D6.patch711 bytes

Add index on provider, id columns for improved performance. For a PURL table with ~15,000 entries, this decreases query time from 13ms to 0.5ms for queries like the following (which occur in purl_load). A busy site may execute millions of such queries each day.
SELECT * FROM {purl} WHERE provider = ? AND id = ?

bblake’s picture

bblake commented
Status: Active » Fixed

Looks like this patch has already been applied to the 6.x-1.x branch. Thanks for your help.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

dbehrman’s picture

dbehrman commented
Issue summary: View changes
StatusFileSize
new add_id_index-1349744-4.patch712 bytes

Modifying this patch to just create an index with the id column. In most cases the provider column would have an extremely low cardinality and this index would be more performant when the provider is not provided.