ZohraS

Sorry, I had so much to do I completely forgot about this.

I went with WP Rocket. It worked seamlessly, didn’t really even have to configure things, it just worked beautifully.

I don’t get what was causing it. It was either one or the other, and the client was furious, since it was a news website. I just had to switch. Sorry man.

mbrsolution : I tried. Nothing.

Stefano Lissa : I did that, then, I got the Facebook sharing issues. That’s the thing, no matter how I went, it didn’t work as planned.

I finally had to take the plugin off the website, and go with a paid alternative. With your plugin in place, the site would usually load at about 4 seconds and a half. With this new one, we’re under two seconds.

I don’t know if it’s the architecture of the server (Nginx + Apache) that was the thing making everything go down, but I just couldn’t keep using a plugin that was giving me so many issues.

Arnan, from what I saw, the little snippet added by taeke in the php wasn’t added. I also added that one earlier. I’m looking at the stats now, and it’s like a miracle happened. I’ve had 2 “spikes” in the last 9 hours. Both of them were about 8000ms (like I said before, the older ones were 58000ms). If the 4 coming tests (usually HUGE spikes at 9:13, 9:44, 10:24 and 10:45) come up the same, I’ll probably be dancing like a dumbass.
No looping JS, already looked. I pretty much already looked at anything that would be the cause, and came up empty, which was frustrating as hell. I’ve developed thousands of WP sites, I usually can tell where the issue is, but this one’s got me completely stumped. I did a couple of other things, but, for some reason, the second I added taeke’s code and cleaned the old data, the site started flying.
Trust me, I’m also confused as to why a plugin would cause that, but this is what I’m seeing at the moment. And dear god, I hope it lasts, cause then, no moving to a new server, and no work during the holidays for me. ?? I’ll keep you posted!

200K rows for 3 ads seems right for you?

I pretty much tried everything, and the little snippets of code from here already helped a LOT.

The site is being monitored, how about I come back tomorrow, after 24 hours of the change, and I’ll let you know how the responsiveness etc has been impacted, and we’ll work from there?

Sorry, this is the one it changed:

Time: 0.5ms (0.0004580020904541s)
    Query: SELECT <code>timer</code> FROM <code>wp_adrotate_tracker</code> WHERE <code>ipaddress</code> = '41.225.162.15' AND <code>stat</code> = 'i' AND <code>bannerid</code> = '5' ORDER BY <code>timer</code> DESC LIMIT 1;
    Function: W3_Db->query()

Arnan, I’m trying to optimize a site that’s killing the server it’s on. The response time was at 58s at its peak.

The wp_adrotate_stats_tracker is more than 200k rows long, and I only have 3 ads. Like I said earlier, my wp_adrotate_tracker was like 30 megs, and the little indexes I created from this thread
dropped it down to 700 or so rows.

This is what Debug objects tells me for adrotate queries:

` Time: 52.5ms (0.052487850189209s)
Query:
SELECT
SUM(clicks) as clicks,
SUM(impressions) as impressions
FROM
wp_adrotate_stats_tracker
WHERE
ad = 5
AND thetime >= 1340409600
AND thetime <= 1371945600
;
Function: W3_Db->query()`

That’s not right, is it? I had a similar result for wp_adrotate_tracker, and now, it’s down to this:

Time: 0.1ms (0.00010395050048828s)
    Query: SELECT
    <code>id</code>,
    <code>bannercode</code>,
    <code>tracker</code>,
    <code>link</code>,
    <code>image</code>,
    <code>timeframe</code>,
    <code>timeframelength</code>,
    <code>timeframeclicks</code>,
    <code>timeframeimpressions</code>
    FROM
    <code>wp_adrotate</code>
    WHERE
    <code>id</code> = '5'
    AND <code>type</code> = 'active'
    ;
    Function: W3_Db->query()

Since I added those two indexes on timer and ipaddress, my response time dropped to about 30ms, with a max of 8 secs when I was changing things around. Any clue as to why it’s doing that?

First of all, thanks so much! You’ve started solving one of the issues I was having on a site. The first index addition doesn’t work for me though, in the sql, it tells me

The following indexes appear to be equal and one of them should be removed: ad, ad_idx

Any idea how to make it work? I need the wp_adrotate_stats_tracker to be optimized like wp_adrotate_tracker (which dropped from like 30 megs to 250kbs!). Thanks in advance!