PHP and MySQL, the future

Written by Brian Moon, dealnews.com developer, on June 27, 2006

The tried and true MySQL extension (mysql) has been around in PHP for years. Lately, there has been a lot of buzz in the PHP world about the new "MySQL Improved Extension" (mysqli) and "PHP Data Objects" (PDO). When I work on Phorum or for dealnews.com, I am always concerned with performance. So, I thought I would give these new methods a try.

What is mysqli?

The PHP manual says

"The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above."

That is as far as it goes, so I am not sure which exact features we are talking about. Looking through the function list, I see things about charsets, encoding, transactions and prepared statements.

Another plus of mysqli is that it includes an OOP interface for those that like OOP. Although, after using it and looking at the docs, I was disappointed that all the work could not be done in 100% OOP. There are several times when calling a function is needed to check for errors and the like. At least, that is how the examples in the manual do it. Because I am not a big OOP user, I decided to stick with the procedural usage in my tests.

(After some hunting, I found an article at Zend.com that discusses mysqli and its improvements.)

What is PDO?

The PHP manual says

"The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions."

Like mysqli, it also supports prepared statements. However, for MySQL, it does not support everything that mysqli does. The most noticable is charset and encoding functionality. However, for new users (especially those from Perl, ASP, etc.) it is probably a good starting point.

Prepared statements

The MySQL manual says this about prepared statements:

"The MySQL client/server protocol provides for the use of prepared statements. This capability uses the MYSQL_STMT statement handler data structure returned by the mysql_stmt_init() initialization function. Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed to prepare it for execution. Then it is executed one or more times at a later time, using the statement handle returned by the initialization function."

On of the big pluses I have heard about prepared statements is that it reduces (according to some, eliminates) sql injection flaws. So, I additionally tested mysqli and PDO in the select tests with prepared statements for that reason.

The nitty gritty tests

I ran two different tests. In all cases, test runs were made several times. The best score for each extension was recorded. No odd spikes or dips occured. Each extension stayed within a 10% range on each run.

Select Speed

The first was selecting 100 rows from a table. For this test, I used a query from the Phorum source code that is used on every message list page.

select
phorum_messages.author, phorum_messages.datestamp, phorum_messages.email,
phorum_messages.message_id, phorum_messages.meta, phorum_messages.moderator_post,
phorum_messages.modifystamp, phorum_messages.parent_id, phorum_messages.sort,
phorum_messages.status, phorum_messages.subject, phorum_messages.thread,
phorum_messages.thread_count, phorum_messages.user_id, phorum_messages.viewcount,
phorum_messages.closed
from
phorum_messages use index (list_page_float)
where
modifystamp > 0 and forum_id = 12 and status = 2 and parent_id = 0 and sort > 1
order by
modifystamp desc
limit 0, 100

Because I work on Phorum and other web based applications, I used openload to test these scripts on a web server. Timing a single query would not yield as much useful data. All the raw data is in raw_selects.txt.

extension req/s
------------------------
mysqli 164
mysql 162
PDO 88
mysqli (prepared) 86
PDO (prepared) 81

I was very disappointed in PDO's speed. However, I was glad to see that mysqli was basically identical to mysql (both varied from the 150's to 160's). It is clear however, that selects using prepared statements is not fast at all. I also found the syntax for using mysqli prepared statements to be cumbersome for selecting data.

Insert Speed

The next test was to insert 10,000 rows into a table. Each insert inserted a random amount of data into the char field and the text field. The table was built as such:

CREATE TABLE `testing_mysql` (
`id` int(10) unsigned NOT NULL auto_increment,
`char_field` varchar(255) NOT NULL default '',
`text_field` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM

MySQL supports what it calls extended inserts. Basically, you can send one query with multiple values sets. If you use --opt with mysqldump, it creates dumps using this format. I decided to test using this method and normal methods of all 3 extensions. In addition I tested prepared statements for mysqli and PDO.

extension time
--------------------------
pdo (extended) 18.17s
mysqli (extended) 18.40s
mysql (extended) 18.46s
pdo (prepared) 25.04s
mysqli (prepared) 36.93s
mysqli 50.43s
mysql 53.09s
pdo 59.96s

This was pretty much what I expected. Using extended inserts puts all the pressure on the database server. As you can see, using that format is fastest and basically the same across all extenstions. If you don't want to use that method, then prepared inserts are a sure fire winner. PDO was the clear winner for prepared inserts. That is a little surprising. I would expect the mysqli extension to be a port of the C mysql api and therefore have the least overhead.

Conclusion

Performance

For all around best performance, you could use either mysql or mysqli. Given they are so close, I consider mysqli the better choice. It gives you the best options on top of the speed. In my opinion, PDO is not for use on systems where mysql performance is a top goal. The only reason I would have for ever using PDO would be to write a generic ANSI-SQL standard driver for Phorum.

Features

For features, I think mysqli is the clear winner. With the ability to set connection preferences and other new features, it is much better than the mysql extension. The only thing PDO offers over mysqli is a more complete OOP interface. However, it lacks some database interaction features that mysqli includes.

Ease of use

The mysql extension is tried and true. There are lots of books and docs out there about it. That makes it still attractive for some. Also, many ISPs will not have support for mysqli or PDO as of yet. The good news is that the mysqli extension looks very much like the mysql extension. Just a little rework of parameters is all that is needed to convert from one to the other.

PDO is great for new users coming from ASP, Perl or any other language that has a single unified system for database interaction. It is simple to use and the syntax is quite logical and sensible. OOP users will love it as it is a 100% OOP interface. I would not recommend it however for power users or applications where high performance is a must.

Disappointment

One disappointment in this test was prepared statements and SQL injection. I had never used prepared statements or read the specifics about them. MySQL (and I assume other database servers) limits what can be prepared and substituted in a query. You can only substitute for things like the values of WHERE clauses and VALUES sets. Unfortunately, for PHP applications, that is not always the only places you need variable data. LIMIT clauses often change from one page to another for paging applications. This variable data must still be set using a string concatenation. So, while prepared statements can help with SQL injection, it is not the end all be all of SQL injection prevention. Its uses is really limited to insert statements only.

And the winner is...

In closing, I am very impressed with mysqli. It lived up to its name. It truly is mysql - improved. Its as fast as the mysql extension ever was and offers more features than PDO. Its OO interface is not as complete as PDO, but the bulk of interaction is there for those that like OOP. If you don't care about OOP, then there is absolutly no down side.