Problem 1 in the article at IBM developerWorks kind of rub me the wrong way.

1. Using MySQL directly

Why not? How is the second code example in that post superior to the first? What I see is that the first example, that uses mysql_* directly, is more readable. The second snippet is a jumbled mess.

The article recommends using PEAR::DB. Is PEAR::DB even PHP5 ready? I don’t know, I don’t use it. The author could have at least used PDO. Still slower, but not as bad and is the future of PHP database abstraction IMO.

He uses a prepared statement to do the select. First, I have tested and I know is slower for MySQL. Second, it is not the end all be all of stopping cross site scripting that a lot of people believe it to be. You can only pass certain value parts of certain …

Life has been super busy, but I have plenty of posting to catch up on. Fear not, there will be more content soon (after Monday, hopefully); I am working on editing a very relevant book, and I hope to be able to share my excitement after I finish.

Also on Monday is the next Boston MySQL User Group, which will go through how to do capacity planning in MySQL with very little pain and effort. In the beginning we will have 10 minutes for user questions, so we can all benefit from each other’s knowledge. I already have a user with a great question!

We have rebuilding our product all summer, with a deadline of releasing the product in the next 2 months. Our lead developer had put a surrogate key in our new schema about a month ago. He said he needed the surrogate key because “the ORM requires it.” I just accepted it.

My mistake was that I made an assumption. The table already had a primary key, but it was a composite key. I …

If you have used the MySQL Federated engine, you may share my opinion that it's perhaps the most amazing feature introduced in recent versions, far more interesting and promising than other more advertised ones.
If you have used it, though, you must have noticed how skinny is the official manual. There are countless questions popping from your head while testing this feature, and no answers in sight.

Article about database design problems is being discussed by Kristian.

Both article itself and responce cause mixed feellings so I decided it is worth commenting:

1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I have not yet tested PDO but would not expect it to beat MySQLi in speed. It is however bad idea to use mysql_ functions directly as well - I would go for using mysqli object approach. The great things about objects is you can easily overload methods and get debugging and profiling tools, as well as have tools which protect you from …

Here’s a good “Addendum/Appendix/Annotation” to the original article by IBM DeveloperWorks. Along with some updated ways of doing things using MySQL 5 and stored procedures.
Updated: August 11th 11:33 AM Thanks to ds- on #php.thinktank, actual addendum is here.

Tags: php, mysql, performance

Today I've upgraded MySQL Server on the host running MySQL Performance Blog. MySQL 4.1.12 was running here for well over a year before that.

Why Have not I upgraded before ? Well because it just worked fine. Yes I know there were some security fixes but I have dedicated server with remote MySQL access closed by firewall and only trusted people having local access so I was not worried too much about it.

Why did I upgrade now ? Because new forum application was exposing one of the bugs in MySQL 4.1.12 so there was a need for upgrade.

Which Version did I upgrade to ? I Upgraded to MySQL 4.1.21. Why Did not I go with MySQL 5.0 ? There are three reasons. First there are number of changes in 5.0 some of which are incompatible with MySQL 4.1, for example regarding join syntax. I can't be 100% sure none of my applications will be affected and I see no reasons to spend time testing it carefully or downgrading if problem finally …

In Five Common PHP database problems, Jack Herrington writes about five common database problems that occur in PHP applications -- including database schema design, database access, and the business logic code that uses the database -- as well as their solutions

My experience in the field differs slightly from his observations, and so I thought a few annotations may be in order.

Continue reading "Annotations to "Five Common PHP database problems""

Tailrank has a few large tables in our MySQL database that could benefit from a bit of truncation for (hopefully) some performance gain. The theory goes that if you have less data you'll have a faster system. This isn't always a one-to-one comparison of course because if you delete enough data MySQL will eventually just buffer the whole data in memory and you'll get an order of magnitude more performance.

All things being equal though what kind of performance boost would you get for SELECTs if you were to take a 20M node table and truncate it by 80%.

The answer is that you'd only receive a 9.3% performance boost. Since btrees are log(N) this means that in order to compute the boost you'd can just use the equation:

(log(N) - log(N_after) / log(N)) * 100

This boost obviously isn't worth the hassle and we're just going to migrate to a new table schema …

Someone at work pointed out to me this blog post about what to do when disk partitions housing MySQL databases fill up, with the caption, “An idea for [the MySQL 5.1 Partitioning] Docs?”

My reply was, “Nope”.

Well, I was wrong. In a way. This guy’s talking about disk partititions, not partitioned tables. And using symlinks to point to data or database directories is not particularly new or exciting. However, partitioning in MySQL 5.1 provides a kind of cool new way to move table data and index files pretty easily.

The DATA DIRECTORY and INDEX DIRECTORY options have been around for CREATE TABLE for quite some time, but they’re not supported for ALTER TABLE - if you try, this is what happens:

 
mysql> …

I've released another version of the innotop MySQL and InnoDB monitor. As always, you can download innotop from the original article.

It's worth upgrading to this version not only because of the new features, but also because it should handle more special cases without crashing. Of course, if it does crash, I appreciate your help fixing it; see this article about what information I need.