Wedge

OK, so let's step through it.

The first row indicates that the first clause found 41,438 rows. It matched those rows with the WHERE, no index was used for it, it did the filtering work in a temporary table, and that temporary table was dumped into a file to sort through it.

The second row indicates it's working on the u table, and that it was using a WHERE clause again, but this time the join buffer was involved - it's using the primary key of u table to filter/find rows. That's good, and it turned up 5 relevant rows quickly.

The third row indicates that it's working on the ua table, but that it just worked on the entire table through a table scan, and apparently returned the entire table as results.

The fourth row indicates it's working on the a table, of any possible indexes it could use, it was using the primary key's index, referring back to the other table, and returned a single row.


The primary reason for the performance drain is the filesort. When you break it down into two queries, you're doing ordering still, but this time you're ordering only integers - the result can be done totally in memory, doubly so because it's a fixed-width row to be working on, and the ordering therein is actually able to be done as you go along.


@Nao, it sort of is a bit weird but I might have to lend you my copy of High Performance MySQL ;)

Re likes: I already outlined a bit back what configuration items are still outstanding, I just haven't gotten round to implementing them yet :P

Re caching, might as well make one cache with everything else. Post-count groups will have a badge (or badges) but probably no colour, non-post-groups will almost certainly have both a badge and a colour. And if we're ever looking at displaying both a badge for group and the stars for post groups (which would be neat, actually), we will need both anyway.

Randomly today I came across https://github.com/pedromenezes/jQuery-lazyload

Might see about making that a plugin sometime.

Yup. User created groups would tend towards being secondary groups where the colour wouldn't inherit anyway.

Also, note to self: need to migrate the list of who likes a post to a proper popup rather than a tooltip, it doesn't work properly with people who have funny symbols in their names as the font Windows uses for tooltips does not include all fancy characters.

I haven't seen any ghost drafts in a while and I've been using it fairly heavily ;)

Not so much posting drafts, maybe, but really for PM drafts I think it'd be beneficial.

Also on an unrelated note, while discussing something over PM, the idea came to me that we could track the number of drafts that are in use for a given member, and display that as a notice variable for the PMs and posts, so that you'd know you had drafts around.

The person I was talking to was not that enthusiastic, hahahaa, but I thought I'd suggest it generally in case others like the idea.

Well, here's the thing, if you load all the groups as I indicated (using 0 to indicate a group whose badge will not be displayed), you do a single query, then fetch the additional_groups and do the processing PHP-side without extra queries or effort, and then when member-created groups get done, they also get a badge-position of 0 which means to exclude it.

Then you're done in all respects and don't even have to tackle FIND_IN_SET.