From b7fa51ae7dd5fef2d9459100c88479413ddd3fb3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Mon, 10 Jul 2017 21:32:25 +0200 Subject: Added given_url in entry table - Added index on entry table for given_url field - Fix tests: The previous `bit.ly` url redirected to doc.wallabag but that url doesn't exist in the fixtures. I used our own internal "redirector" to create a redirect to an url which exist in the fixtures. Also, updating current migration to use the new `WallabagMigration`. --- src/Wallabag/CoreBundle/Repository/EntryRepository.php | 1 + 1 file changed, 1 insertion(+) (limited to 'src/Wallabag/CoreBundle/Repository/EntryRepository.php') diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 880e7c65..299b0b27 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -368,6 +368,7 @@ class EntryRepository extends EntityRepository { $res = $this->createQueryBuilder('e') ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl) + // ->orWhere('e.givenUrl = :url')->setParameter('url', $url) ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) ->getQuery() ->getResult(); -- cgit v1.2.3 From f3bfb875e94021a93e24a41fbc0f8d86d4dee378 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Wed, 29 May 2019 14:18:04 +0200 Subject: Use hash given url to avoid duplicate Using hashed url we can ensure an index on them to ensure it's fast. --- src/Wallabag/CoreBundle/Repository/EntryRepository.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/Wallabag/CoreBundle/Repository/EntryRepository.php') diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 299b0b27..8b29aad2 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -368,7 +368,7 @@ class EntryRepository extends EntityRepository { $res = $this->createQueryBuilder('e') ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl) - // ->orWhere('e.givenUrl = :url')->setParameter('url', $url) + ->orWhere('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl) ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) ->getQuery() ->getResult(); -- cgit v1.2.3 From 70df4c335965a9562cc24d3ccea0a6ed1a23b7b1 Mon Sep 17 00:00:00 2001 From: Jeremy Benoist Date: Wed, 5 Jun 2019 10:51:06 +0200 Subject: 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. --- src/Wallabag/CoreBundle/Repository/EntryRepository.php | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'src/Wallabag/CoreBundle/Repository/EntryRepository.php') diff --git a/src/Wallabag/CoreBundle/Repository/EntryRepository.php b/src/Wallabag/CoreBundle/Repository/EntryRepository.php index 8b29aad2..7772e0b7 100644 --- a/src/Wallabag/CoreBundle/Repository/EntryRepository.php +++ b/src/Wallabag/CoreBundle/Repository/EntryRepository.php @@ -366,9 +366,20 @@ class EntryRepository extends EntityRepository */ public function findByHashedUrlAndUserId($hashedUrl, $userId) { + // try first using hashed_url (to use the database index) $res = $this->createQueryBuilder('e') ->where('e.hashedUrl = :hashed_url')->setParameter('hashed_url', $hashedUrl) - ->orWhere('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl) + ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) + ->getQuery() + ->getResult(); + + if (\count($res)) { + return current($res); + } + + // then try using hashed_given_url (to use the database index) + $res = $this->createQueryBuilder('e') + ->where('e.hashedGivenUrl = :hashed_given_url')->setParameter('hashed_given_url', $hashedUrl) ->andWhere('e.user = :user_id')->setParameter('user_id', $userId) ->getQuery() ->getResult(); -- cgit v1.2.3