Currently working on a new EE site that I've inherited, and having difficulty optimizing performance. Everything was dandy until I finished imported all the old content. Now there are ~700 page entries which cause some pages to take ~18 SECS to render!
I have pages entries setup where you can checkbox multiple categories that the page would belong to. User can select multiple categories which a resulting page widget would filter and display entries that correspond with the selected categories.
Categories(example)
- News
--Featured
--Internal
--External
- Year
--2013
--2012
-Product
--Food
--Cars
--Tech
I have a filter template (filter_template) that was made that apparently sorts through all page entries and displays content. I also have a higher level widget which passes the params to the template. *categories is a WB Category Select type. probably a plugin from whom I inherited from.
{embed="includes/filter_template" _cat_ids="{categories}{cat_id}|{/categories}"}
--> passes to
{exp:channel:entries entry_id="{embed:_entry_ids}" category="{embed:_cat_ids}" limit="200"...}
*Do stuff
{/exp:channel:entries}
Looking through online forums, people are saying to convert the calls directly to ee sql queries. However, for the life of me, I'm really bad at SQL...
EDIT: I've managed to get the queries used by EE by using the output profiler. Now my question is how can I use this direct query within my page, so I can bypass EE channel calls?
/*Grab All Entries*/
SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id WHERE t.entry_id !='' AND t.site_id IN ('1') AND t.entry_date < 1384799111 AND (t.expiration_date = 0 OR t.expiration_date > 1384799111) AND exp_categories.cat_id = '58' AND t.status = 'open' ORDER BY t.sticky desc, wd.field_id_61 desc, t.entry_date desc, t.entry_id desc LIMIT 0, 200
/*Grab relevant data text, links etc... of entries*/
SELECT DISTINCT(t.entry_id), t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id,
w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled,
m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
md.*,
wd.*
FROM exp_channel_titles AS t
LEFT JOIN exp_channels AS w ON t.channel_id = w.channel_id
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id
LEFT JOIN exp_members AS m ON m.member_id = t.author_id
LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id WHERE t.entry_id IN (605,244,...717) ORDER BY t.sticky desc, wd.field_id_61 desc, t.entry_date desc, t.entry_id desc
SELECT field_id, field_name FROM exp_category_fields WHERE site_id IN ('1')
/*Grab category-filter information*/
SELECT c.cat_name, c.cat_url_title, c.cat_id, c.cat_image, c.cat_description, c.parent_id,
p.cat_id, p.entry_id, c.group_id , cg.field_html_formatting, fd.*
FROM (exp_categories AS c, exp_category_posts AS p)
LEFT JOIN exp_category_field_data AS fd ON fd.cat_id = c.cat_id
LEFT JOIN exp_category_groups AS cg ON cg.group_id = c.group_id
WHERE c.cat_id = p.cat_id
AND p.entry_id IN ('605','244'...'717') ORDER BY c.group_id, c.parent_id, c.cat_order
Any help for a web dev just trying to piece it together?