Use of CAST() causes extreme slowdown in large MySQL sites
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Mahara |
Fix Released
|
High
|
Robert Lyon | ||
1.6 |
Fix Released
|
High
|
Unassigned | ||
1.7 |
Fix Released
|
High
|
Son Nguyen | ||
1.8 |
Fix Released
|
High
|
Unassigned |
Bug Description
Mahara version 1.5.2
Linux CentOS release 5.8
PHP Version 5.3.15
MySQL 5.0.77
When editing a page and trying to add a normal text box by dragging it into the page, this loads for approx 2 minutes or more and then eventually appears.
It happens with a journal too but all others are fine and are instant as they should be.
I'm not getting any apache log errors for this nor general server errors. The only thing I am able to see is the query that it hangs on for this length of time.... it is the below...
I hope someone can help as obviously this is causing quite a lot of issues for the users!! Anyone able to diagnose what is the issue here?
The thing is, there is another exact version of the Mahara site alongside this one but just a blank version which runs perfectly fine so this must be an issue within the database somewhere or the maharadata.
Query below: this hangs for about 1 minute 45..
SELECT a.*, CAST(a.owner IS NOT NULL AND a.owner = '1739' AS UNSIGNED) AS editable FROM "artefact" a
LEFT OUTER JOIN "artefact_
OR a.id IN (
)
OR a.id IN (SELECT artefact FROM "artefact_
OR a.institution IN ('test','mahara')
) AND artefacttype IN('blog')ORDER BY title ASC LIMIT 10 |
Then this one for the rest of the time until eventually the text box or journal appears on the page:
SELECT COUNT(*) FROM "artefact" a
LEFT OUTER JOIN "artefact_
OR a.id IN (
)
OR a.id IN (SELECT artefact FROM "artefact_
OR a.institution IN ('test','mahara')
) AND artefacttype IN('blogpost') |
Thank you for your help
Changed in mahara: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
milestone: | none → 1.7.0 |
Changed in mahara: | |
milestone: | 1.7.0 → 1.8.0 |
Changed in mahara: | |
milestone: | 1.8rc1 → 1.8.0 |
Changed in mahara: | |
milestone: | 1.8.0 → 1.8.1 |
summary: |
- Adding Journal or Text box to a page takes approx 2 minutes to load + Use of CAST() causes extreme slowdown in large MySQL sites |
tags: | added: mysql optimization |
Changed in mahara: | |
importance: | Medium → High |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
no longer affects: | mahara/1.9 |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
ok........ we have managed to fix this in case anyone was wondering..........
it was happening just due to the huge size of the database..
we added two new indexes to the below tables:
artefact_ access_ role
artefact_access_usr
index type = index, column = artefact and saved...
all of the sudden - instant results...
maybe something to add into the database for installations in future! :-)