storage/h2: optimize H2 database performance

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

1 files changed, 8 insertions, 0 deletions

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);