aboutsummaryrefslogtreecommitdiffhomepage
path: root/app/DoctrineMigrations
diff options
context:
space:
mode:
authorJeremy Benoist <jeremy.benoist@gmail.com>2019-06-05 10:51:06 +0200
committerJeremy Benoist <jeremy.benoist@gmail.com>2019-06-05 10:53:15 +0200
commit70df4c335965a9562cc24d3ccea0a6ed1a23b7b1 (patch)
treea0c398645e5d340940cf25fab9cab46eb7903e86 /app/DoctrineMigrations
parentf3bfb875e94021a93e24a41fbc0f8d86d4dee378 (diff)
downloadwallabag-70df4c335965a9562cc24d3ccea0a6ed1a23b7b1.tar.gz
wallabag-70df4c335965a9562cc24d3ccea0a6ed1a23b7b1.tar.zst
wallabag-70df4c335965a9562cc24d3ccea0a6ed1a23b7b1.zip
Use two indexes instead of one for hashed urls
When using `OR` in a where clause, a composite index can't be used. We should use a `UNION` to take advantages of it. Instead, create 2 indexes on each hashed urls and make 2 queries to find an url. It'll be faster than the previous solution.
Diffstat (limited to 'app/DoctrineMigrations')
-rw-r--r--app/DoctrineMigrations/Version20190601125843.php26
1 files changed, 3 insertions, 23 deletions
diff --git a/app/DoctrineMigrations/Version20190601125843.php b/app/DoctrineMigrations/Version20190601125843.php
index 341d64dc..0e97606e 100644
--- a/app/DoctrineMigrations/Version20190601125843.php
+++ b/app/DoctrineMigrations/Version20190601125843.php
@@ -30,27 +30,8 @@ class Version20190601125843 extends WallabagMigration
30 ]); 30 ]);
31 } 31 }
32 32
33 $entryTable->dropIndex('hashed_url_user_id'); 33 // 40 = length of sha1 field hashed_given_url
34 $entryTable->addIndex( 34 $entryTable->addIndex(['user_id', 'hashed_given_url'], 'hashed_given_url_user_id', [], ['lengths' => [null, 40]]);
35 [
36 'user_id',
37 'hashed_url',
38 'hashed_given_url',
39 ],
40 'hashed_urls_user_id',
41 [],
42 [
43 // specify length for index which is required by MySQL on text field
44 'lengths' => [
45 // user_id
46 null,
47 // hashed_url
48 40,
49 // hashed_given_url
50 40,
51 ],
52 ]
53 );
54 } 35 }
55 36
56 /** 37 /**
@@ -68,7 +49,6 @@ class Version20190601125843 extends WallabagMigration
68 $entryTable->dropColumn('hashed_given_url'); 49 $entryTable->dropColumn('hashed_given_url');
69 } 50 }
70 51
71 $entryTable->dropIndex('hashed_urls_user_id'); 52 $entryTable->dropIndex('hashed_given_url_user_id');
72 $entryTable->addIndex(['user_id', 'hashed_url'], 'hashed_url_user_id', [], ['lengths' => [null, 40]]);
73 } 53 }
74} 54}