Serendipity Forums

Hi,

the table "serendipity_referrers" contains all referrers which ever visited a blog. During performance analysis of my blog i found out, that the runtime for this query:
is constantly increasing, because this query has to scan the whole table each and every time. That's bad

Can this query somehow modified to not scan the whole table but instead use an index?

Hi!

AFAIK a full table scan is required due to the "SUM(count)", so it cannot be sped up with an index. You could delete old referrers though:

DELETE FROM serendipity_referrers WHERE day <= (unix timestamp of the day you want all earlier entries be removed)

HTH,
Garvin

Yes, of course, i could do that. But this only fixes the problem for my blog - and i don't have a very high number of visitors. The same problem applies to every s9y blog so it would make sense to find a general solution.

What about a regular (cron)job which removes old entries, based on some configuration settings in the blog?

How about turning off the option to enable referrer tracking?

Dom Chambers: you asked this question to me?

ads wrote:Dom Chambers: you asked this question to me?

Yes, sorry. My suggestion is only valid if you do not need the info. I have personally never found any significant benefit from this particular table, so I always disable the configuration option which enables referrer tracking. I presume this eliminates the query, but have never looked at the code to be certain.

I want this feature enabled. But scanning through all old rows seems useless.

Hi!

Currently, there are a few tasks that you need to perform on s9y database tables that no plugin currently does. Most importantly, truncating spamblocklog tables, referrers and visitors.

I'd appreciate if someone wants to develop a maintenance plugin; I even believe someone did a rough work on that a few months ago, but I haven't heard of any final results...

A cronjob is hard because most s9y users do not know how to set one up, and tasks that can take up a long time most probably won't work with the s9y crontab plugin...

Regards,
Garvin

Added to my basket.

Let's see, how deep i can dive into the s9y code ...

I made such a plugin. Not a cronjob, but a cleanup on click:
http://board.s9y.org/viewtopic.php?f=10 ... ndb#p92554

If there is demand, I'd enhance it if necessary if someone tests it (I did on my system and it worked), introduce it on the plugin-system and eventually upload it to spartacus.

Some configuration settings would be nice, also a way to call this script from cron (like the update checker for the plugins).

I would love to test this.

Hi
Improved it a bit - added the referrers-table and made the tables to clean selectable. What's missing is the cronjob, maybe a better optical design, and a configurable timespan of entries not to be deleted. Anything else?

I won't work on it over the long weekend, so thought it's a good time to show the latest version and get your feedback. Are tables missing, working so far for you? You probably are able to help with the sql