Brad Fitzpatrick (bradfitz) wrote in lj_dev,
Brad Fitzpatrick

speed idea

I had my first good idea in awhile. I talked with Dormando about it on AIM.... following is our conversation. One huge problem right now is non-public entries and making people's friends pages load a bunch of crap that they can't even see anyway.

(20:44:06) bradfitz: dude dude
(20:44:10) bradfitz: i have a mad cool idea!
(20:44:14) dormando: ok
(20:44:18) bradfitz: okay okay
(20:44:19) bradfitz: i'll type
(20:44:22) bradfitz: since uh
(20:44:24) bradfitz: phones suck
(20:44:28) dormando: I just walked around my room, no good signal
(20:44:41) bradfitz: man, i hate typing
(20:44:45) bradfitz: my phone was 4 bars
(20:44:49) bradfitz: is yours dead?
(20:44:50) dormando: mine's barely 1
(20:44:53) bradfitz: okay
(20:44:59) bradfitz: typing..
(20:45:04) bradfitz: we have that userusage table, right?
(20:45:08) dormando: yeah
(20:45:16) bradfitz: and it has "timeupdate"
(20:45:19) bradfitz: which means any post
(20:45:20) bradfitz: so you post
(20:45:25) bradfitz: and all your friendofs load you first, right?
(20:45:31) bradfitz: even though they can't read it?
(20:45:36) dormando: yeah.
(20:45:38) bradfitz: so i was thinking.... (this is wrong, but listen anyway)
(20:45:45) bradfitz: we can have "publicupdate"
(20:45:52) bradfitz: which you look at if you're not a friend
(20:45:55) bradfitz: But that's too specifc
(20:46:00) bradfitz: and i was trying to make indexes
(20:46:03) bradfitz: and nothing worked
(20:46:07) bradfitz: BECAUSE I REALIZED....
(20:46:12) bradfitz: when you use custom security
(20:46:15) bradfitz: or any security
(20:46:25) bradfitz: you're really updating 1-30 different journals
(20:46:33) bradfitz: ya see where i'm going with this?
(20:46:36) bradfitz: we need to track this:
(20:46:38) dormando: kind of.
(20:46:49) bradfitz: (ownerid, securitybit) -> timeupdate
(20:46:58) bradfitz: and then you load one of those
(20:47:06) bradfitz: if you subscribe to journalid 5555
(20:47:11) bradfitz: you really are subscribing to 555/*
(20:47:15) bradfitz: but when 555/24 updates
(20:47:20) bradfitz: you only wanna load 555/24
(20:47:21) bradfitz: not 555/*
(20:47:34) bradfitz: so that reverse list we make...
(20:47:42) bradfitz: on friend itemid generation:
(20:47:44) bradfitz: we'd load:
(20:47:48) bradfitz: dormando/public
(20:47:51) bradfitz: foo/34
(20:48:00) bradfitz: bob/public
(20:48:02) bradfitz: sdf/public
(20:48:04) bradfitz: foo/22
(20:48:08) bradfitz: (end)
(20:48:21) bradfitz: but we'd never try to load dormando/1 if that user wasn't a friend of
(20:48:33) bradfitz: then we have an index in logmask over (ownerid, bit)
(20:48:36) dormando: ok.
(20:48:39) bradfitz: (ownerid, bit), rlogtime
(20:48:42) bradfitz: so we can order on it
(20:48:48) bradfitz: and have an 8 byte const prefix
(20:48:51) bradfitz: and order on the last 4 bytes
(20:49:17) bradfitz: ya see?
(20:49:25) dormando: I think so.
(20:49:36) bradfitz: just need to revise 1 table and make one new one
(20:49:44) bradfitz: an alter on log won't be necessary
(20:49:50) bradfitz: then we can drop one table also
(20:49:52) dormando: oh?
(20:50:00) bradfitz: logsec
(20:50:02) dormando: I'm a little confused as to how this data will be represented
(20:50:06) bradfitz: and maybe userusage
(20:50:37) dormando: we can alter log to get rid of the security enum, right?
(20:50:43) bradfitz: okay... there's a table which maps journalid + bit to itemids
(20:51:14) bradfitz: we could, but i'd prefer to keep it for now, since it is only wasting 1 byte per record
(20:51:14) dormando: ok.
(20:51:17) bradfitz: it's not an index
(20:51:28) dormando: alright.
(20:51:31) bradfitz: and it'd involve changing so much code
(20:51:40) bradfitz: where we do $l->{'security'} eq "usemask"
(20:51:40) bradfitz: and shit
(20:51:42) bradfitz: ya know?.
(20:51:46) dormando: yeah
(20:51:57) bradfitz: but when i was making first version of logmask table i was doing:
(20:51:59) bradfitz: INSERT INTO logmask SELECT itemid, ownerid, IF(security='public',1<<31,allowmask), rlogtime FROM log WHERE ownerid=96696;

(20:52:00) dormando: it'll change eventually, but this sounds like you can make these changes quickly where it matters?
(20:52:14) bradfitz: so allowmask of 0 is private, 1 is friends, other is custom, 1<<31 is public
(20:52:26) bradfitz: i'm hoping so
(20:52:28) dormando: yeah.
(20:52:29) bradfitz: god i wish i had a dev server
(20:52:39) dormando: they never got back out today, huh? :\
(20:52:40) bradfitz: goathack's too slow over a modem and my laptop is dead
(20:53:04) bradfitz: so anyway, exlpaining this more:
(20:53:07) dormando: ok
(20:53:16) bradfitz: say user B goes to look at their friend page
(20:53:21) bradfitz: and they have users A and C on there
(20:53:28) bradfitz: user A lists them as not a friend
(20:53:45) bradfitz: so it'd only load A/1<<31
(20:53:48) bradfitz: public
(20:53:58) bradfitz: well, it might not even load it
(20:54:06) bradfitz: it'd first do this:
(20:54:12) bradfitz: okay, i'll start earlier
(20:54:17) dormando: eheh
(20:54:19) bradfitz: there's a new table, let's say userusage2 (uu2)
(20:54:25) bradfitz: it is:
(20:55:02) bradfitz: CREATE TABLE uu2 ( userid INT, bit TINYINT, timeupdate, UNIQUE (userid, bit))
(20:55:22) bradfitz: so say you post with groupmask 12
(20:55:26) bradfitz: 8 & 4
(20:55:39) bradfitz: it then updates bit 3 and 2
(20:55:43) bradfitz: as updated recently
(20:56:04) bradfitz: public is bit 31
(20:56:10) bradfitz: private is bit 0
(20:56:18) bradfitz: so far so good?
(20:56:22) dormando: yeah
(20:56:32) bradfitz: then user A goes to view their friends page
(20:56:39) bradfitz: right now we get the list of all friends and their timeupdate
(20:56:53) bradfitz: well now we'd do the same, but we'd get the timeupdate for subjournal (bit)
(20:56:59) bradfitz: all users have 32 jouranls, essentially
(20:57:19) dormando: Yeah.
(20:57:37) bradfitz: then we throw out bits remote doesn't have access to see
(20:57:40) bradfitz: or don't load it to begin with
(20:57:48) bradfitz: (fun SQL. :P)
(20:57:54) bradfitz: then we load the stuff in order
(20:57:59) bradfitz: like we do now
(20:58:08) bradfitz: but taking advantage of a new table:
(20:58:09) bradfitz: logmask
(20:58:11) bradfitz: like:
(20:58:27) bradfitz: CREATE TABLE logmask (
PRIMARY KEY (itemid),
INDEX tripkey (ownerid, bit, rlogtime)
(20:58:34) bradfitz: so 8 byte const prefix
(20:58:40) bradfitz: then we order on it all
(20:58:50) bradfitz: (oh, need revttime probably... blah)
(20:58:57) dormando: hmm
(20:58:59) bradfitz: then we load shit, throwing out dup itemids
(20:59:18) bradfitz: thoughts?
(20:59:27) dormando: thinking
(20:59:39) bradfitz: brb, keep thinking.
(21:01:09) bradfitz: back
(21:01:19) dormando: I think it could work
(21:01:37) dormando: seems almost roundabout for another issue, but that looks like my brain missfiring
(21:01:52) bradfitz: now the restriction we'd want to impose is that a given allowmask on a post had no more than 'n' bits on
(21:02:12) bradfitz: but from looking at the data, people VERY RARELY use more than 1 bit
(21:02:20) dormando: ok.
(21:02:25) bradfitz: they treat it like radio buttons, not check buttons
(21:02:29) dormando: so we can set that to 3 or so easily?
(21:02:38) bradfitz: the most i saw was 2, actually
(21:02:41) bradfitz: 3 works fine
(21:02:43) dormando: heh.
(21:02:48) bradfitz: i'm sure some people have 30 groups with 1 person each
(21:03:01) bradfitz: actually, evne that wouldn't hurt
(21:03:06) bradfitz: so maybe no restriction is necessary
(21:03:47) dormando: hmm....
(21:03:54) dormando: I can't think of anything offhand that's especially bad with it
(21:05:57) dormando: if this can reduce that stupid query to a single shot, I'll love it to death.
(21:05:59) bradfitz: having to implement it?
(21:06:03) dormando: yeah.
(21:06:10) bradfitz: which stupid query?
(21:06:24) dormando: the one with the backoff which this whole thing is replacing
(21:06:29) bradfitz: i'm going to keep playing on marklar, importing subsets of the data
(21:06:33) dormando: ok
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded