Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Joining the bigint pageId on the string sakaiId can be very bad on MySQL

Description

findItemsInSite has custom SQL (not Hibernate) that performs a join from the lesson_builder_pages pageId (bigint) on lesson_builder_items sakaiId (varchar(250).

An explain is attached showing the terrible MySQL performance.

Attachments

2

Activity

Show:

Matthew Jones November 22, 2017 at 3:49 PM

Merged into 12.x, cherry-picked commit from 315b35fa62e5aab4ad9bccd7908f2ee6f2debdde for 11.x.

Sam Ottenhoff October 23, 2017 at 3:46 PM

This is live in prod at large institutions

Sam Ottenhoff October 3, 2017 at 12:24 PM
Edited

Yeah, pretty similar ..... you're still using a temporary table and sorting ..... once you get 10x more items, that query should be pretty horrible for you

Matthew Buckett October 3, 2017 at 12:12 PM

We have < 1million lesson_builder_items and had the original query take 500ms against our production DB. However our explain is different.

Fixed

Details

Priority

Affects versions

Fix versions

Components

Assignee

Reporter

Labels

Created October 3, 2017 at 11:27 AM
Updated April 18, 2018 at 7:30 AM
Resolved October 4, 2017 at 9:18 AM