I'm seeing unusually long running / slow queries in a relatively straightforward template:
my @bio_stories = ref($story)->list({
element_key_name => 'bio_story',
});
# Filter for just those that have the 'national' element checked
my @national_bio_stories = grep { $_->get_data('national') } @bio_stories;
# Sort by last name
@national_bio_stories = sort { lc($a->get_data('last')) cmp lc($b->get_data('last')) } @national_bio_stories;
This seems to create a query that runs forever to lookup those ~1500 stories:
SELECT s.id, s.uuid, s.priority, s.source__id, s.usr__id, s.element_type__id, s.first_publish_date, s.publish_date, s.expire_date, s.current_version, s.published_version, s.workflow__id, s.publish_status, s.primary_uri, s.active, s.desk__id, s.site__id, s.alias_id, i.id, i.name, i.description, i.story__id, i.version, i.usr__id, i.primary_oc__id, i.slug, i.cover_date, i.note, i.checked_out, group_concat( DISTINCT m.grp__id ), group_concat( DISTINCT c.asset_grp_id ), group_concat( DISTINCT w.asset_grp_id )
FROM story_instance i, element_type e, story__contributor sic, story_member sm, member m, story__category sc, category c, workflow w, story s
WHERE s.id = i.story__id AND sm.object_id = s.id AND m.id = sm.member__id AND m.active = '1' AND sc.story_instance__id = i.id AND c.id = sc.category__id AND s.workflow__id = w.id AND s.current_version = i.version AND i.checked_out = ( SELECT checked_out FROM story_instance WHERE version = i.version AND story__id = i.story__id ORDER BY checked_out
I'm also getting a syntax error when I try to run this through ANALYZE EXPLAIN:
ERROR: syntax error at end of input
LINE 3: ...= i.version AND story__id = i.story__id ORDER BY checked_out
Any thoughts on the above greatly appreciated.
Phillip.
--
Phillip Smith
http://phillipadsmith.com
my @bio_stories = ref($story)->list({
element_key_name => 'bio_story',
});
# Filter for just those that have the 'national' element checked
my @national_bio_stories = grep { $_->get_data('national') } @bio_stories;
# Sort by last name
@national_bio_stories = sort { lc($a->get_data('last')) cmp lc($b->get_data('last')) } @national_bio_stories;
This seems to create a query that runs forever to lookup those ~1500 stories:
SELECT s.id, s.uuid, s.priority, s.source__id, s.usr__id, s.element_type__id, s.first_publish_date, s.publish_date, s.expire_date, s.current_version, s.published_version, s.workflow__id, s.publish_status, s.primary_uri, s.active, s.desk__id, s.site__id, s.alias_id, i.id, i.name, i.description, i.story__id, i.version, i.usr__id, i.primary_oc__id, i.slug, i.cover_date, i.note, i.checked_out, group_concat( DISTINCT m.grp__id ), group_concat( DISTINCT c.asset_grp_id ), group_concat( DISTINCT w.asset_grp_id )
FROM story_instance i, element_type e, story__contributor sic, story_member sm, member m, story__category sc, category c, workflow w, story s
WHERE s.id = i.story__id AND sm.object_id = s.id AND m.id = sm.member__id AND m.active = '1' AND sc.story_instance__id = i.id AND c.id = sc.category__id AND s.workflow__id = w.id AND s.current_version = i.version AND i.checked_out = ( SELECT checked_out FROM story_instance WHERE version = i.version AND story__id = i.story__id ORDER BY checked_out
I'm also getting a syntax error when I try to run this through ANALYZE EXPLAIN:
ERROR: syntax error at end of input
LINE 3: ...= i.version AND story__id = i.story__id ORDER BY checked_out
Any thoughts on the above greatly appreciated.
Phillip.
--
Phillip Smith
http://phillipadsmith.com
