storage/h2: optimize H2 database performance

- use LEFT JOIN instead of making multiple queries
- put insert/update inside transactions
- add indexes
- fix configuration description

2 files changed, 9 insertions, 1 deletions

diff --git a/resources/config.edn b/resources/config.edn
index 180e6c1..04b9b60 100644
--- a/resources/config.edn
+++ b/resources/config.edn
@@ -10,7 +10,7 @@
  :allowed-target-domains ["example.com"]
  ;; available storage types: 
  ;; - :edn (:data-path should point to an edn text file)
- ;; - :sqlite (:data-path should point to a SQLite db file)
+ ;; - :h2 (:data-path should point to a H2 db, WITHOUT extension like ".db")
  ;; :data-path can be non-existent. weepinbell will create them.
  :storage-type :edn
  :data-path "data/webmention.edn"
diff --git a/resources/sql/h2_init.sql b/resources/sql/h2_init.sql
index 2ca12bd..475987f 100644
--- a/resources/sql/h2_init.sql
+++ b/resources/sql/h2_init.sql
@@ -30,3 +30,11 @@ create table if not exists proxied_photo (
     references webmention(id)
     on delete cascade
 );
+
+create index idx_webmention_hash on webmention (hash);
+
+create index idx_webmention_pair on webmention (wm_source, wm_target);
+
+create index idx_webmention_domain on webmention (wm_target_domain);
+
+create index idx_webmention_uri on webmention (wm_target_domain, wm_target_path);