AdRotate query issues (easily solved too!)

yea thats interesting.. i should try that maybe this weekend.. indexing does speed querys thanks for sharing

We’ll look into your idea and do some tests.

I’m not really familiar with indexes. Can you email me at [email protected] so we can discuss this further? If you have time that is. It would be much appreciated!

Thanks!

Where do you put the above code?

Newchik – there are two ways to do it.

First, if you have shell access, you can run mysql from the command line and enter it in that interface. (That is pretty tricky so keep reading)

Second, if you have phpMyAdmin, select your database and then click the SQL tab and paste the query in the textbox and click Execute.

Ed

Thank you- I got it done ??

Add an index on timer also,
CREATE INDEX timer_idx ON wp_adrotate_tracker (timer);

there is quite some improvement with an index on ipaddress and timer

We have been experiencing loads of issues with AdRotate on one of the largest Dutch blogs,
what seems to work is a modification of adrotate-functions.php

On line 395 of adrotate-functions.php trackerdata gets cleaned, we noticed loads (hundreds of thousands) of ‘unknown’ ip addresses in adrotate_tracker we therefore added the following line to adrotate-functions.php:

see pastebin: https://pastebin.com/ryesWc1j

This immediately improved the site performance, no more locked tables or slow queries..
Not sure if Arnan approves or sees problems by adding this line though

This issue has been addressed in the upcoming version by not running the cleanup as much and put it on a schedule to run only once every 3 hours.

But, the ‘unknown’ ip address didn’t stick out in our setups so perhaps it’s useful to add it as a precaution… to avoid flooding the db in the long run.

FOr more info what i’m working on see here: https://adrotateplugin.com/page/get/changelog.php

Yeah, I noticed the changelog and the upcoming changes, very good!
The indexes and this particular trick solved many headaches..

I will do some tests, however, the sites i have aren’t that heavy on traffic so for things like this i’m very dependent on community input since the mentioned issues often are not noticeable for me.
If i don’t get feedback or complaints an issue can exist for months without me knowing about it.

Input like this is very very very helpful!

An idea that is forming now is to not save unknown ip’s at all, they’re useless for the timers anyway. So why bother storing them.

Can you email me a few records of those unknown IP’s so i can write a few lines to exclude them from being saved at all? If you include real IP’s you might wanna obfuscate them.

[email protected]

Thanks!

@aran – I am very looking forward to this release as I was going to remove Adrotate due to performance; yes…it has been great with low traffic but now I notice performance issues with bigger traffic & P3 Profiler shows Adrotate taking up 1/3 of all plugin runtime!

For now I might deactivate, however I am very interested to know when you release your update! ??

We are testing a stable beta now on various servers.
No clear eta on when it will be released though.

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!

ad_idx is not something AdRotate uses.
Indexes have been a part of AdRotate for months now. So I’m not sure what you’re trying to do.

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?