CODEWITHSUNDEEP
phpmysqlmysqli
magallanes
magallanes

Reputation: 6854

PHP Mysql vs Mysqli in windows

I am using PHP 5.3.10 in windows (windows 7 64bits) using Apache and Mod_php.

Im in the process to decide which library i should use, so i am testing both, Mysql and MySQLi

I created two pages for the test

$mysqli = new mysqli("127.0.0.1", "root2", "secretsquirrel", "test");
for ($i=0;$i<100;$i++) {
    $result = $mysqli->query("SELECT * from test");
    // var_dump($result);
    echo "ok $i";
    $result->close();
}

And

$dbh=mysql_connect("127.0.0.1","root2","secretsquirrel",true);  
mysql_select_db("test",$dbh);
for ($i=0;$i<100;$i++) {
    $result=@mysql_query("SELECT * from test",$dbh);
    echo "ok";
    mysql_free_result($result);
}

In both test, i can connect without any problem and can fetch information.

However, if i do a concurrent test (5 concurrent users), MySqli is painfully slow.

And worst, if i do a concurrent test (10 concurrent users), then MySQLi crash Apache.

Faulting application name: httpd.exe, version: 2.2.22.0, time stamp: 0x4f4a84ad
Faulting module name: php5ts.dll, version: 5.3.10.0, time stamp: 0x4f2ae5d1
Exception code: 0xc0000005
Fault offset: 0x0000c7d7
Faulting process id: 0x1250
Faulting application start time: 0x01cd037de1e2092d
Faulting application path: C:\apache2\bin\httpd.exe
Faulting module path: C:\php53\php5ts.dll
Report Id: 1fb70b72-6f71-11e1-a64d-005056c00008

With MySql, everything works perfectly, even with 1000 concurrent users.

Question: i am doing something wrong?.

It is my php.ini configuration

[MySQLi]
mysqli.max_persistent = -1
;mysqli.allow_local_infile = On
mysqli.allow_persistent = On
mysqli.max_links = -1
mysqli.cache_size = 2000
mysqli.default_port = 3306
mysqli.default_socket =
mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =
mysqli.reconnect = Off

ps: As espected, PDO is way worst

The code: (may be the test is wrong?)

$dbo = new PDO("mysql:host=127.0.0.1;dbname=test", "root2", "secretsquirrel" );
for ($i=0;$i<100;$i++) {
    $dbo->query("SELECT * from test");
    echo "ok $i";
}

The result is worst than MySQLi

Update:

I did the same in Linux (redhat) and MysqlI /PDO are more stable.

(1000 concurrent calls, with less there is not any difference).

Module Sum(ms) Min(ms) Max(ms)

MySQLi 66986 265 1762

MySQL 64521 234 1388

PDO 75426 249 1809

(minus is better).

Well, apparently there is not a answer, under windows MysqlI and PDO is a big no (unless to development process). For linux, the three are the same, however for a popular server (a lot of concurrent users), Mysql is the best, MysqlI is close (3% slower) and PDO is a big no (+10% slower).

However, it is not a real test so mileage can vary. However, the results are consistent with the popular believe, Mysql>Mysqli>pdo.

Upvotes: 4

Views: 2058

Answers (2)

Anoop Pete
Anoop Pete

Reputation: 493

Apache May Not Support everything, I've Faced Many Problems with Respect To Password Encryption & Decryption With Respect To Apache, but found solution on Hosting it on IIS...

Try Running your Application in IIS... http://learn.iis.net/page.aspx/246/using-fastcgi-to-host-php-applications-on-iis/ will helps you to HOST PHP on IIS 7... http://www.websitehosting.com/apache-vs-iis-web-server/ will clear the difference between Apache & IIS with respect to PHP...

Upvotes: 0

tdammers
tdammers

Reputation: 20706

The single feature in mysqli that should make all the difference is parametrized queries. The mysql interface doesn't have these, which means you will be interpolating values into queries yourself, and this in turn means you have a big potential for SQL injection vulnerabilities - it just so turns out that securing your query concatenation isn't as trivial as it sounds.

BTW, have you considered PDO? It offers the same features mysqli does, but it can connect to any of the supported (and configured) databases, so if at any point you decide to migrate to, say, PostgreSQL, SQLite or SQL Server, you have only the SQL dialect differences to worry about, instead of porting everything to a different API.

Upvotes: 1

Related Questions