Thread

Hello,
I have a website with around 70000 channel entries and am having huge performance issues.
I am already using caching wherever I can which helped a bit.
After some debugging I wanted to ask you about a query, which really stood out:

2.1843 SELECT t.entry_id, t.title, t.url_title
                FROM (exp_channel_titles AS t)
                LEFT JOIN exp_channels AS w ON w.channel_id = t.channel_id  WHERE t.entry_id != 121973  AND t.entry_date < 1347368390  AND t.entry_date <= 1344345162  AND IF (t.entry_date = 1344345162, t.entry_id < 121973, 1)  AND (t.expiration_date = 0 OR t.expiration_date > 1347368390)  AND w.site_id IN ('1') AND w.channel_name = 'network' AND t.status = 'open'  ORDER BY t.entry_date DESC, t.entry_id DESC LIMIT 1

Why is this query taking so long?
Is there any way to speed it up / am I doing sth wrong?
EE 2.4.0

Thnx

Hi soundseller,

Thanks for posting the debugger! That is pretty useful.

Why is this query taking so long?
Is there any way to speed it up / am I doing sth wrong?
EE 2.4.0

We need to track that query back to the channel entries tags that are making the call. Can you sniff out the {exp:channel:entries} tags that pull that query?

Even with 70,000 total entries, ExpressionEngine will only pull 100 by default.

We need to see that tag to get a handle on the time.

Cheers,

Thnx for your reply,
here we go:
http://pastie.org/private/dpcvw2b7jgacaxjarpzftq

Hi soundseller,

The culprit is likely CE Image. That is a great add-on, but it needs lots of RAM to be efficient. Can you check the RAM configuration for CE Image?

The reason it can be a factor lies with the source images it’s processing. If there are a lot, it can take a while to churn through them.

It looks like you are trying to cache effectively. Are you using CE Image’s caching as well?

I look forward to your reply.

Cheers,

Hello,
thnx for your reply.
CeImage is not causing the issue.
I am using it in another channel, that has far less entries, and everything runs smoothly.
There is definitely something going “wrong” when 50000+ entires are involved.
The next/previous tags queries take forever.

Hey soundseller,

That changes things a bit.

I’m going to bring this up with our Engineers and see if there is an answer in the code.

You may get a request for more details as part of that process, but that would come via email.

Thank you for your patience!

Cheers,

Thank you.

Hi soundseller,

The lag is coming from around line 163:

<!--Pagination-->
      <div class="pagination clearfix">
       {exp:channel:prev_entry channel="{segment_2}"}
        <div id="prev_entry">
           <a href="/blog/{segment_2}/{url_title}class=prev_link">{exp:eehive_hacksaw chars="25" append = "..."}{title}{/exp:eehive_hacksaw}</a>
        </div>
       {/exp:channel:prev_entry}

       {exp:channel:next_entry channel="{segment_2}"}
        <div id="next_entry">
         <a href="/blog/{segment_2}/{url_title}class=next_link">{exp:eehive_hacksaw chars="25" append = "..."}{title}{/exp:eehive_hacksaw}</a>
        </div>
       {/exp:channel:next_entry}
      </div>

Your channel next/previous tags should be *outside* of channel entries.

Can you move that in your template and run your tests again?

Cheers,

Thanks,
I moved the block outside the channel:entries tag but it still takes the query more than 2.3 seconds to finish:

2.3145    SELECT t.entry_id, t.title, t.url_title
                FROM (exp_channel_titles AS t)
                LEFT JOIN exp_channels AS w ON w.channel_id = t.channel_id  WHERE t.entry_id != 125927  AND t.entry_date < 1348042560  AND t.entry_date <= 1347663606  AND IF (t.entry_date = 1347663606, t.entry_id < 125927, 1)  AND (t.expiration_date = 0 OR t.expiration_date > 1348042560)  AND w.site_id IN ('1') AND w.channel_name = 'network' AND t.status = 'open'  ORDER BY t.entry_date DESC, t.entry_id DESC LIMIT 1

I will just deal with it for now.
The issues might also be related to another module, which I am using “Solspace’s tags/related entries”, which is also slowing down the loading times significantly.
Haven’t found a way around that either, yet.

You can mark this thread as resolved.

Hey soundseller,

I am glad that Dan was able to help! That’s awesome!

If you need anything else, please just let me know by opening a new thread.

Cheers,