[wp-hackers] Use of ENGINE=MEMORY w/ dbDelta() possible?

[Mahmoud Al-Qudsi]

> Not individual files, that was as a whole folder of files.
> 
> But 4KiB isnt much data really, its definately not enough to store
> multiple reasonable sized blog entries.
>
> Which is why it was splitting it into 4 chunks, and then over multiple
> rows if need be.

Point taken.. 
 
> Also, doing a delete for any row thats expired should be really quick
> with a memory table, If need be, you could even only run it on
> (rand(0,5) == 5), but i'd just take the slight performance hit myself
> and delete old rows, if you use a datetime, or a int field, it should
> be rather fast.

Yeah, I agree... especially if the datetime field is indexed. I'd rather delete old rows when they're next accessed, something like

DELETE * FROM cache WHERE date<$date;
SELECT `value` FROM cache WHERE `key`=$key;

An important thing is to execute all simultaneous queries in the same call to $wpdb->query(); since *ANYTHING* that can reduce the MySQL overhead here is a good thing. (this isn't done in the old code, which executes the DELETE first, then goes back and executes the INSERT) 

Another possibility is to let PHP do the checking:
SELECT `value`, `age` FROM cache WHERE `key`=$key

Then have PHP check if `age` is less than $now-3600 or whatever. If it is, it'll return false and this entry will be overridden by the new data, no extra query needed.

> As a final note on this.. Is it worth running a mysql memory cache like
> this if you've got access ot mysql itself.
> There are a few settings which allows mysql to cache queries much more
> efficiently, so that repeditive queries from WP are stored in memory..
> http://weblogtoolscollection.com/archives/2005/04/06/optimize-your-
> database/
> http://www.arnebrachhold.de/2007/02/16/four-plus-one-ways-to-speed-up-
> the-performance-of-wordpress-with-caching/ ("MySQL Query Cache")

This doesn't require access to the MySQL configuration files, and *should* run anywhere that WP can/does.

But the bottom line is that caching to MySQL is horribly inefficient.

-MQ