X-Cart forums

[intel352]

[UPDATE] - After several months of using this code with no issues, I've updated this post with a zip file containing a DIFF (patch file) that can be applied via X-Cart Admin. This DIFF was created using X-Cart 4.1.8

MySQLi is a new extension available in PHP 5, which gives better features and performance over PHP 4's MySQL extension.

I'd suggest reading up about it, and googling mysqli. There are tons of articles about the differences between the 2 extensions.

The code modification is in use at CapeFearStyles, so feel free to visit there to see how the site performs. If you see any issues with the website, or note any performance problems, please let me know

If it proves to be stable & effective, then I'll update this post with a patch for user's to apply to their X-Cart installs.

Anyone that wants to use this patch now, and test it own their own installations while knowing that it's not proven to be stable yet, please send me a Private Message

Cheers

EDIT: btw, CapeFearStyles is running on a server that recently upgraded to PHP 5.2.3 (FastCGI) with MySQLi installed, Apache 2.2.4, MySQL 5.0.45, just as a point of reference

Major Features

ext/mysqli supports the new features found in recent versions of MySQL and introduces a few features of its own.

The major features of the extension are:

* A procedural interface that looks very much like the ext/mysql interface
* An object-oriented interface that allows for a use idiom that is both more convenient and easier to extend than the procedural interface
* Support for the new MySQL binary protocol that was introduced in MySQL 4.1. (The new protocol is more efficient that the old one and allows for the support of a broader range of features, such as prepared statements.)
* Support for the full feature set of the MySQL C client library, including the ability to set advanced connection options via mysqli_init() and related functions.

Additionally, the extension has support for additional tracing, debugging, load balancing and replication functionality.

Why Switch?

Beyond gaining access to the new features of MySQL 4.1+, why would anyone want to switch to using ext/mysqli?

In addition to the functionality mentioned above, ext/mysqli also has some other serious benefits:

* Greater speed. Enhancements in both the extension and in MySQL have made most operations faster, with certain operations becoming up to 40 times faster as compared to ext/mysql.
* Better security. In older versions of the MySQL RDBMS, the possibility existed for an attacker to extract weak password hashes from the network and then recreate a user's password. The new authentication procedure is much more robust and mirrors the attack-resistant authentication procedure of tools like SSH.

[Jayk]

I am quite interested in this. I have contacted my host to see if PHP is built with MySQLi on our server.

Jason

[intel352]

When I get a chance, I'll also see about setting up some benchmark tests for X-Cart so I can test the performance differences on my server (if any). Hopefully it actually results in a performance increase. I'll post the results back here.

Jayk, lemme know what you find out
Fastest way to find out, is to just go into X-Cart Admin, click on Summary, scroll down to where it's giving info about your server, click on More Info (or Details, can't remember) next to PHP, that'll show you your server's PHP Info page. Search for MySQLi, that'll tell you if it's enabled

[inebriate]

if i recall, you need php 5 or higher to use mysqli...

just switching the commands from mysql to mysqli may not show improvements, in fact it may even be slower...you need to keep in mind that mysqli escapes its strings, so there is no need for the mysql_real_escape_string

i am hoping that xcart would get around to using prepared statements

[intel352]

Actually, performance varies based upon various conditions. Typically mysqli seems to perform about the same as mysql, but if you use Prepared Statements, you don't have to escape values, and there is a definite speed increase in MySQLi over MySQL

Additionally, MySQLi only auto-escapes strings used in a Prepared Statement. You still need to use mysqli_real_escape_string to sanitize your data otherwise:
http://www.php.net/manual/en/function.mysqli-real-escape-string.php

[intel352]

And yes, you need MySQL 4.1+, and PHP 5+

[intel352]

A simple preliminary page benchmark using X-Cart's benchmark utility shows pretty much the same performance on the homepage. I'll do more testing later on style & category pages.

Originally Posted by MySQL

[23-Aug-2007 13:15:13] (shop: 23-Aug-2007 13:15:13) BENCH message:
PHP: time: 0.1090 (54.75%); reference points: 67 (37.85%)
SQL: time: 0.0342 (17.2%); reference points: 87 (49.15%)
SMARTY: time: 0.0559 (28.07%); reference points: 23 (12.99%)
Full time: 0.1991
Used memory: 1.091
Max. used memory: 1.272
Max. saved session data: 2.475
Request URI: /home.php
-------------------------------------------------
[23-Aug-2007 13:15:18] (shop: 23-Aug-2007 13:15:18) BENCH message:
PHP: time: 0.0893 (49.92%); reference points: 68 (37.99%)
SQL: time: 0.0344 (19.21%); reference points: 88 (49.16%)
SMARTY: time: 0.0553 (30.88%); reference points: 23 (12.85%)
Full time: 0.1789
Used memory: 0.999
Max. used memory: 1.181
Max. saved session data: 2.475
Request URI: /home.php
-------------------------------------------------

Originally Posted by MySQLi

[23-Aug-2007 13:08:44] (shop: 23-Aug-2007 13:08:44) BENCH message:
PHP: time: 0.0983 (52.91%); reference points: 71 (38.38%)
SQL: time: 0.0326 (17.54%); reference points: 91 (49.19%)
SMARTY: time: 0.0549 (29.57%); reference points: 23 (12.43%)
Full time: 0.1857
Used memory: 1.072
Max. used memory: 1.247
Max. saved session data: 2.475
Request URI: /home.php
-------------------------------------------------
[23-Aug-2007 13:10:01] (shop: 23-Aug-2007 13:10:01) BENCH message:
PHP: time: 0.0897 (50.37%); reference points: 67 (37.85%)
SQL: time: 0.0342 (19.24%); reference points: 87 (49.15%)
SMARTY: time: 0.0541 (30.41%); reference points: 23 (12.99%)
Full time: 0.1780
Used memory: 0.980
Max. used memory: 1.161
Max. saved session data: 2.475
Request URI: /home.php
-------------------------------------------------

[Jon]

Very cool mod.

mysqli is definitely an improvement in many areas and it will be much more common once apps are pushed to php5. mysqli_real_escape_string is a great security feature. The question of course that you are trying to answer is, is it effective given x-cart's code bloat

[intel352]

lol, exactly Jon

[bigredseo]

I know this is an older thread, but we're finally implementing MySQLi on a number of our new servers (not forcing existing users to use the mysqli function), but I have a concern over a particular feature;

persistent connections

We all know X-Cart loves it's connections, and one of the things that we've almost always had (and see recommended here on the forums on a regular basis) is the persistent connections feature.

Since MySQLi doesn't support this, how have people been dealing with this say should they have 200+ visitors on their site at the same time?