New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Slow plugin loading query #8183

Closed
mrclay opened this Issue Apr 12, 2015 · 3 comments

Comments

Projects
None yet
2 participants
@mrclay
Member

mrclay commented Apr 12, 2015

reported by @hypeJunction:

SELECT  e.*, plugin_oe.*, r.id
FROM elgg_entities e
JOIN elgg_private_settings ps on ps.entity_guid = e.guid
JOIN elgg_objects_entity plugin_oe on plugin_oe.guid = e.guid
JOIN elgg_entity_relationships r on r.guid_one = e.guid
WHERE  ps.name = 'elgg:internal:priority'
AND (r.relationship = 'active_plugin' AND r.guid_two = '1')
AND ((e.type = 'object' AND e.subtype IN (2)))
AND (e.site_guid IN (1))
AND ((1 = 1) AND (e.enabled = 'yes'))
ORDER BY CAST(ps.value as unsigned), e.guid 

It's the slowest performing query of all. Much worse than any metadata/annotation query (and there are some heavy ones with 6-10 joins).

Not sure if we should just load all plugins with their private settings in one go, and sort in php?

@hypeJunction

This comment has been minimized.

Show comment
Hide comment
@hypeJunction

hypeJunction Apr 12, 2015

Contributor

It could be the case. I suppose we need to do some benchmarking.
On Apr 12, 2015 6:12 AM, "Steve Clay" notifications@github.com wrote:

reported by @hypeJunction https://github.com/hypeJunction:

SELECT e., plugin_oe., r.idFROM elgg_entities eJOIN elgg_private_settings ps on ps.entity_guid = e.guidJOIN elgg_objects_entity plugin_oe on plugin_oe.guid = e.guidJOIN elgg_entity_relationships r on r.guid_one = e.guidWHERE ps.name = 'elgg:internal:priority'AND (r.relationship = 'active_plugin' AND r.guid_two = '1')AND ((e.type = 'object' AND e.subtype IN (2)))AND (e.site_guid IN (1))AND ((1 = 1) AND (e.enabled = 'yes'))ORDER BY CAST(ps.value as unsigned), e.guid

It's the slowest performing query of all. Much worse than any
metadata/annotation query (and there are some heavy ones with 6-10 joins).

Not sure if we should just load all plugins with their private settings in
one go, and sort in php?


Reply to this email directly or view it on GitHub
#8183.

Contributor

hypeJunction commented Apr 12, 2015

It could be the case. I suppose we need to do some benchmarking.
On Apr 12, 2015 6:12 AM, "Steve Clay" notifications@github.com wrote:

reported by @hypeJunction https://github.com/hypeJunction:

SELECT e., plugin_oe., r.idFROM elgg_entities eJOIN elgg_private_settings ps on ps.entity_guid = e.guidJOIN elgg_objects_entity plugin_oe on plugin_oe.guid = e.guidJOIN elgg_entity_relationships r on r.guid_one = e.guidWHERE ps.name = 'elgg:internal:priority'AND (r.relationship = 'active_plugin' AND r.guid_two = '1')AND ((e.type = 'object' AND e.subtype IN (2)))AND (e.site_guid IN (1))AND ((1 = 1) AND (e.enabled = 'yes'))ORDER BY CAST(ps.value as unsigned), e.guid

It's the slowest performing query of all. Much worse than any
metadata/annotation query (and there are some heavy ones with 6-10 joins).

Not sure if we should just load all plugins with their private settings in
one go, and sort in php?


Reply to this email directly or view it on GitHub
#8183.

@mrclay

This comment has been minimized.

Show comment
Hide comment
@mrclay

mrclay Oct 25, 2015

Member

Ordering in PHP speeds this up considerably. I have only 24 plugins active and dropping the ORDER BY takes me from 3.7ms to .8ms

Member

mrclay commented Oct 25, 2015

Ordering in PHP speeds this up considerably. I have only 24 plugins active and dropping the ORDER BY takes me from 3.7ms to .8ms

mrclay added a commit to mrclay/Elgg-leaf that referenced this issue Oct 25, 2015

perf(boot): we order plugins in PHP because MySQL order by CAST is slow
MySQL can't index for ORDER BY CAST(ps.value) so it is super slow. It's a
lot faster to just usort() in PHP.

Fixes #8183

mrclay added a commit to mrclay/Elgg-leaf that referenced this issue Oct 25, 2015

perf(boot): we order plugins in PHP because MySQL order by CAST is slow
MySQL can't index for ORDER BY CAST(ps.value) so it is super slow. It's a
lot faster to just usort() in PHP.

Fixes #8183

mrclay added a commit to mrclay/Elgg-leaf that referenced this issue Oct 25, 2015

perf(boot): we order plugins in PHP because MySQL order by CAST is slow
MySQL can't index for ORDER BY CAST(ps.value) so it is super slow. It's a
lot faster to just usort() in PHP.

Fixes #8183
@mrclay

This comment has been minimized.

Show comment
Hide comment
@mrclay
Member

mrclay commented Oct 25, 2015

PR #9066

mrclay added a commit to mrclay/Elgg-leaf that referenced this issue Oct 26, 2015

perf(boot): we order plugins in PHP because MySQL order by CAST is slow
MySQL can't index for ORDER BY CAST(ps.value) so it is super slow. It's a
lot faster to just usort() in PHP.

Fixes #8183

@mrclay mrclay closed this in #9066 Oct 26, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment