Skip to content

Improve performance of getting previous version of product during indexing

Martinez, Eric M. requested to merge github/fork/emartinez-usgs/current-product-query-speed into master

  • Whitespace changes

  • Updated bind port in some tests for test-machine purposes

  • Update sql to use faster sub-select when query contains required parameters

  • All tests pass

Example Query Performance

Old

mysql> SELECT DISTINCT id FROM productSummary p WHERE p.id IS NOT NULL AND NOT EXISTS (SELECT id FROM productSummary ps WHERE ps.type=p.type AND ps.source=p.source AND ps.code=p.code AND ps.updateTime > p.updateTime AND ps.status <> 'DELETE') AND source=SOURCE AND type=TYPE AND code=CODE;
+-----------+
| id        |
+-----------+
| XXX       |
+-----------+
1 row in set (3 min 5.82 sec)

New

mysql> SELECT DISTINCT id FROM productSummary p WHERE p.id = (SELECT id FROM productSummary ps WHERE ps.source=SOURCE AND ps.type=TYPE AND ps.code=CODE AND ps.status <> 'DELETE' ORDER BY updateTime DESC limit 1);
+-----------+
| id        |
+-----------+
|       XXX |
+-----------+
1 row in set (0.00 sec)

Merge request reports