performance design flaw in bb_update_meta

[_ck_]

There is an inherited, legacy performance issue in bb_update_meta that causes excessive queries when meta is updated in the db.

There is a failed logic problem that does a SELECT query for the row about to be updated to determine UPDATE/INSERT instead of using ON DUPLICATE KEY, with a key in the table on object_id + meta_key.

Originally I thought maybe meta didn't have a key like that because it was designed on purpose to tolerate multiple meta_keys with the same name and the same object_id. But this makes no sense since bb_update_meta will force all the same object_id + meta_keys to have the same data. So it's pointless, you can't have different data.

The problem with this is, it allows corruption of the meta by accidental creation of multiple rows with the same object_id + meta_key.

BUT the data MUST ALWAYS be the same in such duplicate rows, because update meta does the SELECT query and will then before an UPDATE, with LIMIT to affect all the rows.

The table will then just keep the extra duplicate rows, and the SELECT in the update meta happens over, and over and over again.

This design flaw in update meta causes the queries to double for every plugin that uses it and wastes the mysql cache.

I'd like to see this addressed even in 0.9 as a patch, with a routine to first check the meta tables for duplicates, remove the duplicates, then add the table key index, then replace the bb_update_meta routine to use INSERT ON DUPLICATE KEY instead of SELECT/INSERT/UPDATE.

[_ck_]

Oh and to make matters worse, updating meta (at least topic meta) causes not only the above two queries to execute, but in addition it causes the topic and then the topic meta to re-load, for reasons I cannot fathom.

So a single topic_updatemeta causes no less than FOUR queries.

1. SELECT (to find any existing meta rows to either INSERT or UPDATE)
2. INSERT (or UPDATE, the meta)
3. SELECT (re-load the topic)
4. SELECT (re-load the topic meta)

That's insane.
There should be a table key, an INSERT ON DUPLICATE KEY and the redundant topic re-load should be avoided. I believe it's to re-load the object cache, which should be modified in memory directly by the data it was passed instead.

[sambauers]

INSERT ... ON DUPLICATE KEY may be an option if we decide not to do anything with the idea in #629, basically doing this closes that door pretty much completely. I'm happy to have a conversation about the merits of that other ticket as well.

The second part (from the previous comment) about reloading the topic is quite necessary I think. Firstly, updates don't happen all that often and secondly, the only sure fire way to make sure your cache matches your database after an update is to read from the database again. Considering that topics and their meta are stored as one object in the object cache, the rereading is required unless we create some way to selectively update parts of the cached objects. But since the object cache is fairly generic and... well... dumb, it doesn't have anything like that. In the end rereading is a lot easier from a maintenance of code perspective.

[_ck_]

Does WordPress really work that way? If so, that's insane because it's a tremendous performance hit to not have a mysql key on the meta_key and just serialize the data inside if it's an array.

To update a specific value in such a pseudo array using multiple rows, a plugin author would have to specify the old value and the new value, otherwise all values for the meta_key would be replaced (and duplicated). Is that functionality even in wordpress's update meta? I am not familiar with the newest versions but old versions didn't have it.

[(none)]

Milestone 1.0-beta deleted

[johnjamesjacoby]

Sadly, WordPress suffers this exact same fate, so the bbPress plugin for WordPress does not see an improvement in this area. Closing as wontfix since changing the way meta queries work is highly unlikely due to stand-alone relying on BackPress, and the plugin relying on WordPress.