I've written a lot recently about MySQL index and table structure, primary keys, surrogate keys, and related optimizations. In this article I'll explain how MySQL's index structures enable an extremely important query optimization, and how that differs between storage engines. I'll also show you how to know and predict when the optimization is triggered, how to design tables and queries so it'll be used, and how to avoid defeating it with poor practices. Plus, I'll peek a bit into InnoDB internals to show you what's going on behind the scenes.

MySQL provides two different tools to test the MySQL Server with SQL statements. One is mysqltest and in 5.1 mysqlslap. Both of these tools have quite different purposes. This is a quick review of the usage of mysqltest.

Current Usage

Under Linux deploys, the README in the mysql-test directory gives you all the information you need to know to run.

To run the full test suite.

cd /opt/mysql/mysql-test
 ./mysql-test-run

Rather easy, it does take some time, and I was surprised to find a number of tests marked as ’skipped’. The general purpose of having tests in a product is to provide coverage of software functionality, and tests should always be forward …

Our company, RightMedia is in the process of evaluating a storage subsystem for our ever growing MySQL databases. It seems that our data is growing at an alarming rate (this is a good thing) and our internal SCSI disks aren't handling the load of massive inserts, plus large queries. So the time is now to evaluate a storage subsystem that will grow with us and scale for our performance needs.

I was really reluctant when I first heard iSCSI proposed as a potential solution. I am familiar with large storage subsystems all hooked in through fibre channel. It's an expensive solution, but performance is great. There are a few iSCSI vendors, and we had lots of problems with kernel version & driver version. But, we perservered, and have systems where we can benchmark the performance of iSCSI.

Now before I get to the actual numbers, let me give you the environment for the …

Interview with Mark Spencer:
http://www.asteriskvoipnews.com/asterisk_news/new_mark_spencer_interview_creator_of_asterisk.html

Mark Spencer is the author of Asterisk, the open source PBX system,
and is the primary owner of the company Digium which commercializes
it. Asterisk has the real potential to have a long term ability to
commoditize much of the telecom industry. Used Skype Out? Then your
call was probably routed via Asterisk by one of Skype's partners.

If you are an employee at MySQL then you have used Asterisk since it is our PBX system.

Catch the last question to Mark and his answer:
Question: Why are you planing to release a new stable version every 6
month? Many people have the feeling that a PBX software should …

The new release of PBXT is, in fact, smaller than the previous version. This is due to a major re-structuring of the code. The result is a more compact and direct implementation. With less code to execute and some major changes this version is also a lot faster.

The most significant change is that all files (except the transaction logs) are now associated with a particular table. All table related files begin with the name of the table, and can be easily identified. If a database is not being accessed you can copy a table by copying the associated files, or drop a table by deleting the files. However, the main reason for this change was to improve the speed of sequentially reading and writing.

Records are now divided into a fixed and a variable length component. The fixed length component is stored together with the handle in the record data file. The variable …

As the open source community is gripped in "futbol fever" a huge thank you is owed to Yahoo for hosting the official FIFA World Cup web site.  The site's performance is excellent as it deftly handles huge traffic surges from fanatical fans clicking madly to see results in real time.  As with many Yahoo sites, this one is powered by MySQL.  We're proud to be playing our part here in delivering up-to-date information to fans around the world. 

Today's semi-final match is Germany vs Italy being played in Dortmund. In past World Cup encounters, Italy has always come out ahead, but this time the German team may have momentum --and a home crowd advantage --on their side.  The game is at 3:00 pm US Central Time, 21:00 CET.

On the education front things are moving forward nicely. I now have confirmation that the only thing that stands between me and a degree in computer sciene is finishing my thesis paper. The paper itself is already well under way. I have all the literature research done and I have written the first 2 chapters and I am fairly far with the third chapter. I just send out a ton of email interviews. The general topic is open source in the relational database market. I will publish the paper on this site once I am done.

I am also now officially MySQL 5.0 developer certified. I did the beta exams back in march/april and I passed. Overall the exam felt much more close to real world knowledge than the Zend PHP exam I took last year. I learned a lot of the nitty gritty details of MySQL I have not run into so far. Unfortunately even MySQL 5.0 still has a fair amount of stuff like this. That being said the …

For small ISVs, Google is GOD. A significant percentage of the traffic into our web-store comes through Google. However, too much of Googlism is not always good. Read on?

I run a small ISV, Webyog, which develops and sells MySQL management tools. Most of our sales take place through our online store. I am a strong believer of  Sales Fixes Everything and just like other owners of small ISVs, the most important and ?feel-good? mails for us are those ?sales notification? mails that we get whenever someone makes a purchase.

I live in India and the bulk of our sales take place in USA and West Europe. This means that I am generally sleeping while the majority of our sales are taking place. The first thing that I do in the morning is to download and count the sales notification mails. Sometimes …

Edwin DeSouza had been nagging me about various web pages on the subject of online backup; one, on the Microsoft Developer Network, claims that it's bad to optimize your backup strategy for backup speed.

That's an interesting issue, and one that we thought about early on. We decided that, in general, it is good to optimize your backup strategy for speed. One size doesn't fit all, of course, but:

• The basic issue is that a fast backup can bring a slow recovery in its wake. That's often the case, but not always.
• The impact of a slow recovery depends on how often you do it. Our attitude is that a recovery is a very rare occurrence. The fact that we've got by without an online backup at all for so long reinforces this viewpoint.
• Backups …

I’ve written a lot recently about MySQL index and table structure, primary keys, surrogate keys, and related optimizations. In this article I’ll explain how MySQL’s index structures enable an extremely important query optimization, and how that differs between storage engines. I’ll also show you how to know and predict when the optimization is triggered, how to design tables and queries so it’ll be used, and how to avoid defeating it with poor practices.