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/Entity/Entry.php | 3 ++- src/Wallabag/CoreBundle/Repository/EntryRepository.php | 13 ++++++++++++- 2 files changed, 14 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/Wallabag/CoreBundle/Entity/Entry.php b/src/Wallabag/CoreBundle/Entity/Entry.php index 304dd1b3..19f81c0f 100644 --- a/src/Wallabag/CoreBundle/Entity/Entry.php +++ b/src/Wallabag/CoreBundle/Entity/Entry.php @@ -27,7 +27,8 @@ use Wallabag\UserBundle\Entity\User; * indexes={ * @ORM\Index(name="created_at", columns={"created_at"}), * @ORM\Index(name="uid", columns={"uid"}), - * @ORM\Index(name="hashed_urls_user_id", columns={"user_id", "hashed_url", "hashed_given_url"}, options={"lengths"={null, 40, 40}}) + * @ORM\Index(name="hashed_url_user_id", columns={"user_id", "hashed_url"}, options={"lengths"={null, 40}}), + * @ORM\Index(name="hashed_given_url_user_id", columns={"user_id", "hashed_given_url"}, options={"lengths"={null, 40}}) * } * ) * @ORM\HasLifecycleCallbacks() 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