How to speed up listing jobs that match metadata criteria on a large database

Problem

Due to the large amount of data inside the table containing job metadata (XML documents of various sizes for example) the table has a limited amount of indexes to reduce the size of the database. For large databases this might result in a slower performance when searching through it. Some customer have however requested additional indexes to this table to make certain operations faster when listing jobs matching a given criteria.

GET /job?jobmetadata=key=value

CODE

Solution

As a workaround it is possible to add some optional indexes to the database to speed things up; please note that these all come at the price of an increased size of the database. Proceed with caution.

For PostgreSQL it is recommended that you use what is called partial indexes. The following syntax can be used to add these to the t_jobdata table:

CREATE INDEX t_jobdata_item_idx ON t_jobdata USING btree (c_index, c_data) WHERE c_index = 'itemId';

CODE

In this example "itemId" was used, To find other valid key's you can find these inside the return data using the query parameter, "metadata=true":

GET /job/VX-123?metadata=true

CODE

This will return an XML JobDocument similar to this, in which you can find the appropriate keys you may be able to add as partial indexes if you so wish:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns0:JobDocument xmlns:ns0="http://xml.vidispine.com/schema/vidispine">
    <ns0:jobId>VX-123</ns0:jobId>
    <ns0:user>someuser</ns0:user>
    <ns0:started>2017-09-05T14:15:45.777Z</ns0:started>
    <ns0:finished>2017-09-05T14:15:49.002Z</ns0:finished>
    <ns0:status>FINISHED</ns0:status>
    <ns0:type>PLACEHOLDER_IMPORT</ns0:type>
    <ns0:priority>MEDIUM</ns0:priority>
    <ns0:currentStep>
        <ns0:description>Importing sidecar files.</ns0:description>
        <ns0:number>16</ns0:number>
        <ns0:status>FINISHED</ns0:status>
    </ns0:currentStep>
    <ns0:data>
        <ns0:key>baseUri</ns0:key>
        <ns0:value>http://somehost:8080/API/</ns0:value>
    </ns0:data>
    <ns0:data>
        <ns0:key>bestEffortFilename</ns0:key>
        <ns0:value>cow.jpg</ns0:value>
    </ns0:data>
    <ns0:data>
        <ns0:key>bytesWritten</ns0:key>
        <ns0:value>12495</ns0:value>
    </ns0:data>
    <ns0:data>
        <ns0:key>componentId</ns0:key>
        <ns0:value>VX-114</ns0:value>
    </ns0:data>
...

CODE

In the example above you can see keys such as <ns0:key>baseUri</ns0:key> which is a possible key to add as an index.

For MySQL partial indexing isn't possible, so here it is possible to do what is called a prefix index instead; these index only the leading part of the column value, but at the same time, this indexes ALL the data in this column. To create a prefix index that is limited to the leading 19 characters:

CREATE INDEX t_jobdata_prefix_idx ON t_jobdata (c_data(19));

CODE

Please note that this is also possible for PostgreSQL using:

CREATE INDEX t_jobdata_prefix ON t_jobdata (left(c_data,19));

CODE