[wp-hackers] Network activations and WP SlimStat

[Otto]

Looking through WP Slimstat, I see you're storing a ton of information
on a per blog basis. Stats, visits, and outbound.

Why are these not all in one table each? Why give each blog separate
tables? I mean, you could add a measly blog_id integer and combine all
those tables into a total of 6 tables only, no matter how many blogs
there are.

Things like countries and other "static" bits should be hardcoded.
Does the "countries" table update itself in real time or something? If
not, why have it in the database at all? I see it's pulling from a CSV
file.. Why not change that file into a PHP array and then just load
the array and do the substitution that way?

Another point that comes to mind is that, generally speaking, almost
all of this data is never actually used for anything. A lot of stats
plugins are like this, they store data for every hit to the site, but
then only really display charts, graphs, and other summary type
information. Why not just store the results you're going to display
instead of every piece of data about every hit? Lot of duplicated
information there that seems rather pointless, on the whole.

-Otto


On Mon, Nov 8, 2010 at 1:48 PM, Dino Termini <dino at duechiacchiere.it> wrote:
> Hi Andrew Nacin and all,
>
> I've been following the thread about the new network activation feature
> proposed by scribu on trac
>
> http://core.trac.wordpress.org/ticket/14170
>
> I'd like to briefly comment on the message you left for me here
>
> http://core.trac.wordpress.org/ticket/14170#comment:49
>
>> The reason why I am against this in core is demonstrated precisely by
>> your plugin. Looking through it, you're creating an *awful* lot of
>> tables. You are creating six tables *per blog*. That can't scale in
>> any reasonable fashion.
>>
>
> I understand your concerns about creating so many tables per blog, and I
> will definitely use your hint to make the lookup tables (3 out of 6) global.
> That will bring down the number of tables created to 3 per blog (if we
> exclude the first one). For 100 blogs in a network, there will be 303 tables
> (3 * 100 + 3 global) instead of 600, which is a great improvement I think.
>
> I came up with this table structure for my plugin after a step of
> normalization and optimization of the information, so I'd like you to
> elaborate a little more on why you think this is an *awful* lot of tables. I
> could've come up with 2 tables instead of 6 (one for the geolocation of IP
> addresses, one for storing the hits) but then there would have been an
> *awful* lot of redundancy, with a waste of space and performance. Most users
> are using WP Slimstat in a single-blog environment and some have more than 1
> million hits recorded in their main table. With the structure I'm using, I
> was able to shrink the amount of space needed for 10,000 hits to just 1,4
> megabytes, with each row taking about 150 byte or less.
>
> Andrew, I'm open to rethink this architecture if you can suggest a better
> approach :) In the end, all it matters is to have a plugin that does what it
> is supposed to do, and that does it in the best way possible, right?
>
>> Sidenote - A lot of those are lookup tables and should probably be
>> global tables. The ones that aren't can be post types. Then you won't
>> need to do this at all.
>>
>
> Could you please elaborate on this? Are you saying I should 'clutter'
> wp_posts with 1 million records just to avoid creating a separate table?
> Doesn't this impact the whole performance when WP will have to access that
> table to retrieve a post or a bit of content?
>
> Thank you,
> dino.
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>