[wp-hackers] wp + mysql ?

[Otto]

On Thu, Jun 12, 2008 at 9:35 AM, Daniel Torreblanca
<regulatethis at gmail.com> wrote:
> Aside from prepared statements and any of the implicit benefits
> brought by mysqli, can anyone think of any other explicit advantages
> to switching? Not to diminish the impact of what I just mentioned -
> prepared statements is a great feature.

Prepared statements is the only reason I can think of to switch. And
except in a few cases, I'm not sure that those would help WordPress a
whole lot.

The most obvious place that prepared statements would help would be
the wp_options table. On a complex site, those queries are frequent
and all similar in structure. But for most of the rest, there's not a
lot of benefit. The wp_posts pull is the biggest query, and it's built
on the fly based on lots of factors, so it's not going to gain a
tremendous improvement there.

Security-wise, mysqli is safer both in the use of prepared statements
(explicitly being able to define types for data is a very good thing),
and in the login functionality (mysqli uses the new security / login
model that was introduced in MySQL 4.1).

However, speed-wise, most tests and benchmarks I can find suggest that
mysqli is somewhat slower at straight queries than mysql. The
differences I've seen seem to be significant, but only in the
multi-thousands queries range.

Looking through 2.5.1, I see that there are no mysql_* calls outside
of wpdb right now, so it is entirely possible to convert that file and
use the wp-content/db.php hook thing to replace it on your own site,
if somebody wants to test the idea out and run some benchmarks.
There's only roughly 20 calls to replace, and replacing them is fairly
simple and straightforward to do. However, the benchmarks are likely
to show that mysqli is (fractionally) slower without use of prepared
statements.

And the use of prepared statements is much a bigger challenge. There's
SELECTS and such sprinkled all over the code. A new model would likely
need to be designed for that, one that keeps the prepared statements
somewhere centralized, so that functions can a) create them if needed
and b) reuse them when they get called again.

-Otto