Jeremy Knope dot com

So I decided to do a simple performance test after a friend linked me to this blog post of someone doing a comparison against a PostgreSQL database. I use MySQL at work so I wondered if there was much difference with the mysql drivers for these various APIs. The results actually seem to show PDO to be the winner when querying a MySQL database.

Here’s the results:

This test was run on a Ubuntu PC, an AMD 1800+ (1.53Ghz) PHP 5.2.2 machine. A simple query was run 250 times returned about 270 rows, similar to the blog post I mentioned. The 250 query iterations were also run 10 times per API and the average of that is taken. The results fluctuate a little but are usually pretty close to the same. So it seems like for MySQL PDO does a pretty good job.

Edit: Added Zend_Db results, which uses PDO behind the scenes

Technorati Tags: database, mysql, performance, php, programming

One of the most popular MySQL management tools out there, phpMyAdmin has become widely used, but I’d have to say it’s one of my least favorites. I did use it back in the day and it wasn’t too bad, some of the features it offers are very useful. It’s one of the few tools to let you do things like rename a table simply, or other move-stuff-around type actions. My discontinued use of it started out with a particular update and there after performing horribly slow on my G3 server. I’m assuming this particular problem could possibly be gone now, but it made me ditch it quite fast. This could’ve easily been some other problem for all I know, but first reaction just was to ditch it and use CocoaMySQL instead at the time. I’ve never used it since partly because I got along fine with various other tools, and also my current employer does not use it due to security concerns. From phpMyAdmin being such a popular application, it’s prone to security holes way more frequently than other tools, mostly because of it being a public accessible tool for your MySQL database.

Shots of both phpMyAdmin themes/styles:

Overview of the ups and downs of phpMyAdmin:

Pros

• Access from any machine, no installation of client software needed
• Robust set of features
• SQL file import and export with reasonable options
• Table operations like renaming, moving data etc.
• Simple tools for copying data or structure from database to database
• Display of row data only limited to your browser ability to render that much html (or just use the pagination)
• Full suite of tools covering querying to privleges

Cons

• Uses frames for it’s interface, can be funky sometimes (would be better with no frames, ajax, even iframes maybe)
• Font sizes of default themes bit wonky on latest one I tried, fixable of course (big on default, tiny on dark)
• UI can be a bit sluggish due to it being a web application, depending on connection speeds etc.
• Can be prone to security holes due to popularity, updates are usually quick though
• Can’t do ‘quick edits’ like some desktop clients allow

The power and versatility of phpMyAdmin puts it at the top for MySQL client tools, but mostly out of personal preference I choose to remove it from my list of clients. The problems with phpMyAdmin are pretty slim, and if you don’t mind a web-based MySQL client, then you probably are already using this, or will at least find it suitable. So far I’m pretty convinced that a web-based MySQL management tool is just not something that will ever jive with me, I yearn for the snappier and cleaner UI of a desktop application when it comes to managing my MySQL databases. Unfortunately most of those bring in their own set of problems as seen in MySQL Query Browser Review and soon to come with reviews of YourSQL and CocoaMySQL.

Technorati Tags: client, database, mysql, php, review, software

I’ve decided to do a series of reviews, or maybe a series, on various MySQL client software, primary focus will be on Mac OS X since that’s my platform of choice. I have been in search of a decent MySQL desktop client, since phpMyAdmin does not fit the bill for me, not to mention at work we don’t use it per security concerns.

In comes MySQL Query Browser with probably the better of the UI designs out of the clients I’ve tried, particularly on Mac OS X with a pretty clean design. With tabbed query windows, syntax highlighting, safe row edits, and more Query Browser is a pretty decent regular use MySQL client. What it lacks really is the ability to administer users directly instead of relying on it’s sister application, MySQL Administrator. Also the inability to export an sql dump of a database is also a bit of a let down, another feature of MySQL Administrator.

MySQL Query Browser serves as an excellent tool to edit table structure and test queries. The query window looks like this and support multiple tabs which is very beneficial:

MySQL Query Browser Main Query Window

Table structure editing isn’t bad either, although better on some of the platforms like Windows where more auto-complete works. A sample of a table being edited:

MySQL Query Browser Table Editing Window

Here is a sum up of what’s good and bad about MySQL Query Browser:

Pros:

• Clean UI
• Decent table editing
• Pretty fast
• Handles large amounts of data in view relatively well
• Row editing safe due to having to click edit (this could be a con for some, but I feel less paranoid with it)
• Built-in help
• Query bookmarking (save a cool query for later)
• Query History (go back & forth through it, see cons for a problem with this)
• Simple copy SQL to clipboard feature when clicking a table

Cons:

• Can crash frequently, possibly less on Windows version
• Mac OS X version doesn’t have inline auto-complete for column types (windows one does)
• Foreign key handling is flaky due to crappy errors from MySQL itself (not getting column types EXACTLY matching)
• Lack of SQL dump/export (due to it being featured in MySQL Administrator as ‘backup’)
• Editing rows relies on presence of a primary key, custom queries often data can’t be edited.
• Query history is cool but it executes every time you hit back or next, which sucks for any UPDATE or DELETE queries
• Crashes when your a little to crazy with clicking sometimes (Mac version at least)
• Preferences and saved connections is immensely slow (when accessing to edit or save changes)
• No way to organize connections if you have a ton of them (I’d like that at least…. NO client has that though)

So there is quite a few cons for this, but so far they haven’t out weighed the cons of other clients to make me not wanna use it. I often use it pretty briefly so my experience with it’s problems is usually short-term. Some of the other candidates for consideration and review next are YourSQL, CocoaMySQL, and Navicat. Any other suggestions (taking Mac OS X software and possibly linux software suggestions only) feel free to comment.

Technorati Tags: client, database, mysql, review, software

So I managed to try out MySQL Workbench on Windows to see if maybe that version runs better. Staying true to the drastically different UI for each OS that every MySQL.com tool is like, this one was much different on Windows but seemed to not break so easily. Linux version I even tried but that wouldn’t even start on ubuntu. A promising tool but definitely still beta, but maybe the least beta on windows at the moment.

Here’s a simple shot of the windows version:

Apparently MySQL AB turned out a new tool, or at least new to me, which lets you design data structure in a visual manner, called MySQL Workbench. I was pointed to it yesterday by a friend and never had seen it on mysql.com before. This is a tool similar to software like DBDesigner 4, SQL Editor for OS X, and EOModeler from WebObjects. You create tables and you can link them together via relations using a relation ship line tool, basically creating an ERD that you can output to an SQL file or possibly a database. Now this is kinda cool except that this particular piece of software is beta, and I think when it comes to the MySQL AB tools, this means almost unusable. I must say I’ve liked most of the actual UI design in their tools, and Workbench seems pretty decent too, just falls short at this moment with the beta due to bugs that halt the successful usage of this tool.

MySQL Workbench is available for linux, Windows and Mac OS X and I have only tried the Mac OS X version which very likely might be the only one with the crippling bugs I ran across. I basically couldn’t use the relationship features for generating foreign keys due to some bugs that didn’t let me set foreign keys, let alone remove the lines they generate.

It definitely is a promising tool that could prove to be useful once it’s smoothed out and the bugs that limit it at least on OS X are gone. I’ll be watching it, but I also haven’t had too much use for a tool like this yet, just more want to find a use for one, and maybe once this works better I might find it beneficial.

I’ll provide an update of how it is if the linux version is more usable.

Here’s a simple shot of it:

I just recently finished a ‘beta’ version of a ActiveRecord:Migration wannabe database schema migration system in PHP at work. It allows me to version the database schema/structure easily and allow for easy database structure updates when updating an install of a web app on some site. Eventually it could do downgrades like the rails version too but that’s not something I’ll worry about right away. It does function, isn’t as elegant as AR:Migration but it does the job which is pretty cool. It might join PHPFlash class as one of my most useful chunks of code.