← Back to team overview

mahara-contributors team mailing list archive

[Bug 1889340] Re: Adding indexes to tag table to speed things up

 

To analyze do something like this:

explain analyze  SELECT a.title, p.title AS parenttitle, a.id, a.parent, a.owner, a.author, a.authorname,
                    a.description, a.allowcomments, at.tag, a.ctime, a.mtime
                FROM "artefact" a
                JOIN "artefact" p ON a.parent = p.id
                JOIN "artefact_blog_blogpost" ab ON (ab.blogpost = a.id AND ab.published = 1)
                JOIN "tag" at ON (at.resourcetype = 'artefact' AND at.resourceid = a.id::varchar)
                WHERE a.artefacttype = 'blogpost'
                AND a.owner = (SELECT "owner" from "view" WHERE id = 123) AND EXISTS (
                        SELECT * FROM "tag" AS at
                        WHERE at.resourcetype = 'artefact' AND at.resourceid = a.id::varchar
                        AND at.tag =  'test'
                    ) ORDER BY a.ctime DESC, a.id DESC;

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1889340

Title:
  Adding indexes to tag table to speed things up

Status in Mahara:
  Confirmed
Status in Mahara 19.04 series:
  Confirmed
Status in Mahara 19.10 series:
  Confirmed
Status in Mahara 20.04 series:
  Confirmed
Status in Mahara 20.10 series:
  Confirmed

Bug description:
  To make requests to tags table faster

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1889340/+subscriptions


References