mahara-contributors team mailing list archive

[Robert Lyon <1889340@xxxxxxxxxxxxxxxxxx>]

To test:

Go to a big site and find a view with some tags on it - make a note of
the view id and plug that into the SQL above in place of the '123'

Run that 'explain  ...' sql and make a note of the "Planning time" and
"Execution time" - run it a few time to get an average

Then add the unique index
ALTER TABLE tag ADD CONSTRAINT tag_unique UNIQUE (tag, resourcetype, resourceid, ownertype, ownerid);

Run that 'explain  ...' sql and make a note of the "Planning time" and
"Execution time" - run it a few time to get an average

It should be way faster

Then drop the index so you can add it back with upgrade of the site

ALTER TABLE tag
DROP CONSTRAINT tag_unique;

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