PROCEDURE ANALYSE()

MySQL DBA Certification candidates need to know about a little known but important function.

PROCEDURE ANALYSE() is a rarely used function in these days of inexpensive disks. It is mainly used to suggest optimal column sizes. Forty years ago you needed to worry how to properly encode data so that it would fit on an eighty column punch card. Thirty years ago when hard disks were the size of stove or dishwasher, there was a need to conserve as much space as possible. Today people carry gigs of data in their pocket and a terabyte of disk is available at local stores next to other consumer products. So you can be less than optimal in your storage of data for the most part. There are exceptions. Remember that a smaller width index is going to be a faster index.

Let us pretend that we need to create a table to track item numbers and our data architect, boss, or SWAG tells us there will only be twenty item numbers, ranging from 1 to 20. And by the way, the boss wants efficiency.

mysql> CREATE table x1 (item INT(4) NOT NULL);

No insert the values 1 to 20 and analyze.


mysql> SELECT item FROM x1 PROCEDURE ANALYSE()\G

You will get information on the minimum and maximal values, the minimal and maximal data length, and a suggested optimal field type. In this case a TINYINT(2) UNSIGNED NOT NULL is recommended.

mysql> ALTER TABLE x1 MODIFY item TINYINT(2) UNSIGNED NOT NULL;

You can show your boss that the table is optimized and the rest of the company is so inspired by the news that they start creating more items. Then one day you get a call from two very upset project managers. It seems that item #255 is being used for two items. What happened?

Well, TINYINT UNSIGNED holds the values 0 to 255 and will mangle values over 255 to 255. So you rerun the PROCEDURE ANALYSE() and receive the recommendation to change the filed in question to TINYINT(3) UNSIGNED. But remember TINYINT is not going to let you store anything bigger than 255. So you check the numeric types manual page and go to the next larger data type, SMALLINT.

PROCEDURE ANALYSE() can be a double edged tool. In the first case it helped. In the second case it had no idea you needed bigger values and gave you a recommendation based on bad data. Like most tools, PROCEDURE ANALYSE() needs to be used properly to get the desired results.

Running PROCEDURE ANALYSE() on your tables from time to time can help you spot potential fields getting squeezed for space.

Practice, practice!

There is an old joke about the concert violinist on his first visit to New York City who finds himself lost and late for rehearsal. So he asks a police officer, "How do I get to Carnegie Hall?"

The cop answers, "Practice, practice!"

If you new to the world of databases (or MySQL's world of databases) and want to do well on your MySQL certification tests, one of the best things you can do to internalize the knowledge needed is to practice. And the best practice tool is out there for free at http://dev.mysql.com/doc/ under Example Databases.

The MySQL Student Guides from our excellent training classes and our Certification Guides use the World database in their examples. I encourage you to type in the examples and work through any exercises our questions with this wonderful collection of tables.

Then work up your own exercises. Write a query to display the head of state and capital from each country or find the average population for each square mile and kilometer.

Many of us learned to program in C by typing in the K&R 'Hello world' program (and not doing it right the first time). Working through the examples is worth the time and effort.

------

It is almost time to send out a quarterly newsletter to those of you on the MySQL Certification E-mail list. There are lots of things to be covered such as certifications at the Users Conference, how Sun's purchase of MySQL will make subtle changes in the certification program, and exciting things that I can not even hint about right now.

But are there anything you want to know or want emphasized?

Remember there is no such item 'dumb question', unless you ask how to get to Carnegie Hall

Hint for CMDBA candidates

Many CMDBA (Certified MySQL Database Administrator) candidates give away points on their exams that could be remedied with a half hour of exploration. This is not an easy set of exams but there is something you can do to add points to your score. The way to do this is to install MySQL on a Windows system.

Many in the FOSS world may never actually touch a Windows system. But if you look around, you may be able to find an older, unused system sitting around for this exercise. Many folks have older windows systems gathering dust that you can borrow.

Once you have the Windows box, here is a three part exercise to help boost your exam scores.

One: Download and install the Windows binaries. The install features a wizard to guide you through installation. You will now have an instance of MySQL on your system.

Two: Track down the configuration files and add a second instance.

Three: Get this second instance running.

This simple exercise should not take a hour of your time on even the slowest of Windows systems.

That is it. End of exercise.

Order and guarantees

The Certified MySQL Associate exam is the entry level exam for those new to MySQL and it covers a lot of the basics. Sometimes the basics are so basic that they are not obvious.

Suppose you have a table with dozens of columns that has been in production for a period of time and you issue the statement 'SELECT * FROM long_used_table;'. What order will the records be returned from the server?

Well, it depends. And a second try with the same statement may give you different results. The rule is that there is not a guaranteed order. If you want the data in a specific order you need to add ORDER BY, i.e. 'SELECT * FROM long_used_table ORDER BY special_index;'.

Now, on the CMA exam you are ask about this guaranteed order (or lack there of) in a very explicit fashion. In the other exams, it is not as straight forward and you have better have that internalized when reading the question. And in 'real life', this will catch you every time it can!

Are you hot or not?

Computer Weekly looks at MySQL as a hot skill to have. The full article is at:

http://www.computerweekly.com/Articles/2008/03/22/229539/hot-skills-mysql.htm

We at MySQL hear a constant plea from customers and partners looking for qualified MySQL developers and DBAs. The MySQL exams are the best way to prove your qualifications.

Not certified? Please try the test questions at: http://www.mysql.com/certification/selftest/core/index.php

Default Fault

Have you run into the 'default fault'?

mysql> create table foo (t1 int not null, note char(10) default 'none');

Query OK, 0 rows affected (0.01 sec)


mysql> insert into foo values (1,'some');

Query OK, 1 row affected (0.01 sec)


mysql> insert into foo values (2);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql>

So why isn't the default value of 'none' automatically entered into the table? If you are an old hand at MySQL, you probably are smiling right now and remembering the first time you whacked you metaphorical toe on this situation. There is a question similar to this in the MySQL exam bank that separates the experienced pros from the newbies.

Okay, so how do you get the default value into the note field?

Hint:

mysql> insert into foo (t1) values (2);


Query OK, 1 row affected (0.00 sec)


mysql>


The table foo has two columns. Now 'insert into foo values (...' statement tells the server that you are going to have one column of data for each column in the table, even the ones with a default value.

The 'insert into foo (t1) values ...' statement tells the server you have one column of data and the server will graciously fill other columns with any default values you have specified.

Don't lose points on your next MySQL exam!

Judging by the test scores, the INFORMATION_SCHEMA is unfamiliar territory to many of you. Many other vendors besides MySQL support this standard and it allows you access to data that is unavailable or hard to find otherwise.

Back when I first downloaded the MySQL tarball from tcx.se, I did not know much about DBMS software and just needed a running database. Over the years I upgraded that software and did not dig deeper into the software because I did not need to. I was too busy with other job related duties to worry about my always smooth operating MySQL instances.

But one day the database seemed slow and the regular show commands were not giving me the answers I needed. So how do you dig further? Yup, the INFORMATION_SCHEMA.

The INFORMATION_SCHEMA is the meta-data about your tables. What storage engine, column names, collation, and stored procedures are out there lurking in your database. This is the 'big brother' view about your databases. And since the data is in tables, you can your SQL skills to retrieve the information.

So do some exploration of the INFORMATION_SCHEMA to learn more about your data. And that knowledge can help you the next time you take a MySQL Certification Exam.

---

Once again MySQL will be offering our certification exams at the Users Conference in April. See http://en.oreilly.com/mysql2008/public/content/home for details and be sure to say 'hi'. Last year over 400 exams were taken and space will be limited this year.

DBA Exams and Optimize/Analyze

The MySQL optimize and analyze commands must be pretty confusing if judged by the CMDBA (Certified MySQL Database Administrator) exams. Hopefully this is because MySQL works wonderfully 'out of the box' and does not need a great deal of maintenance. I am looking at the questions on the exams where candidates are not performing as expected and optimize/analyze is a stumbling point.

There are several table maintenance operations that you need to know about to keep your database healthy. Failure to keep your database healthy can slow down queries and do other nasty things to your quality of life.

CHECK TABLE performs an integrity check on the structure and content of your tables. For MyISAM tables, it will also update the index statistics. If problems appear, proceed to ...

REPAIR TABLE is for MyISAM tables only and will correct corrupted tables. InnoDB tables should be dumped with mysqldump and restored.

ANALYZE TABLE updates a table's information about keys for queries so that the data can be found faster and not having to hunt for it. This works for both MyISAM and InnoDB.

OPTIMIZE TABLE defragments MyISAM tables, sorts indexes, and updates the statistics to speed up searches.

Automatic MyISAM repair can be enabled with the --myisam-recover option, either at startup or in a configuration file. You can direct the server to make a backup of any table before it changes, force recovery, or skip tables when then have no gaps from deletes or updates.

InnoDB will try to fix any repairs at startup as part of normal operations. But it is best to read the documentation on --innodb_force_recovery before you need to use it under pressure.

Please read the documentation on these commands and make sure you understand any locking issues before using them. With careful application, these commands can keep your data healthy.

Exam Pointer Number 1

Do you know the difference between client programs and standalone programs?

The MySQL Certification exams have a series of questions about client programs spread among the six exams. The gist of the questions are that MySQL supplies some programs that need to connect to the mysqld server. These programs depend on having a running MySQL server with which to interact.

MySQL also supplies some programs that do not require a running server. The risk adverse would be wise to just shut down their database before using them. The myisamchk table-maintenance utility or myisampack MyIsam table compressor for examples. Read the documentation on myisamchk for the list of woes that can be befall the more adventurous.

Some of the questions on the exams are quite detailed on these various programs. But it is obvious that many candidates are not able to differentiate the two types of programs.

On one of of the exams, just over a third of the candidates could identify the difference between these two types of programs. That is a lot of people that could be damaging their data needlessly.

Reviewing exams

With MySQL 5.1 reaching release candidate stage, I have been reviewing ALL of the MySQL Certification exam questions to hunt down any items that may now have been superseded by the march of progress. And right now the Merge Storage Engine has caught my eye.

With 5.1 comes the ability to disable the Merge Storage Engine. It used to be that you could not disable the MyIsam, Memory, or Merge engines. They were compiled in and could not be disabled. They were a constant, solid and dependable. But now you can pass --skip-merge to mysqld and disable it.

Does this option effect a lot of people? I honestly don't know. But it will change a few questions on the certification exams. And it may make a great piece of knowledge if they ever publish the Trivial Pursuit MySQL Edition.

Sample MySQL Certification exam questions

Test your knowledge of MySQL! There are four sets of questions covering the four MySQL Certifications.

These questions are closely modeled on actual questions from the Associate, Developer, DBA, and Cluster DBA exams.

These questions are for entertainment purposes only. And hopefully those of you lacking your MySQL Certifications will entertain taking the exams!

Your lack of CMDBA Certification may have cost you a great job

Somebody missed a good job by not being being a Certified MySQL Database Administrator (CMDBA). So for the lack of some study time and the cost of two exams, a prime job in New York for a MySQL DBA evaporated. Several times each month I hear from someone internally at MySQL that some customer is looking for MySQL Certified talent.

For a Texan, I have spent a fair amount of time in New York and know there is a good deal of LAMP talent in that area. It is frustrating to know an area with so much talent did not have someone holding a CMDBA that could be found by the company mentioned in the linked blog.

I used to be the CTO of an online recruiting corporation and I know that keywords such as 'MySQL', 'CMDBA', or 'MySQL Certified' are what recruiters are plugging into their searches. Recruiters are looking for you but do you have those keywords linked to your resume? Remember even technical recruiters may not know a DBA from someone who once set up one table from just comparing resumes. But they know what the MySQL Certifications mean.

For those of you who have passed DEV-I or DBA-I and need extra motivation to finish, please get moving. You may not be thinking of changing jobs but knowing you are more desirable in the market place can help you in your current job too.

We recently ran a contest for the best certification story were the contest winner found a great job with better pay two blocks from his house. His new bosses found him by looking at the official list of the MySQL certified. Could your dream job be just around the corner and looking for you too?

So the rest of you, what can I do to help get your rear in gear? I really want to help you get your MySQL Certifications. So what do we need to do together?

Where are MySQL Certifications growing fastest?

MySQL's Certification exams are given in Pearson VUE test centers or at the MySQL Users Conference. Ignoring the numbers from the UC, the growth in exams taken last year was impressive. The Americas and EMEA grew at 25% and 26%. Pacific grew 91%. And in Japan we grew an amazing 166%!

The UC numbers are numbing by themselves. We will be printing in the neighborhood of 500+ exams to cover the six testing periods during the conference. Considering how exciting the speakers and presentations are, having that many people dedicate time in a relatively small room to sit demanding exams is impressive.

Getting the North Texas MySQL Meetup rolling

Meetup.com has lots of MySQL users groups sponsored by MySQL itself. Look for a group near you at www.meetup.com or start one.

I have been trying for the past few months to get the North Texas MySQL Meetup Group rolling. One member who made our first meeting was able to use some knowledge he learned to help earn a Paypal Certified Developer Certification shortly before he got a new job. Sadly the new job kept him away from the last meeting.

If you are in or around Dallas / Fort Worth, we will be in the Starbucks inside the Barns & Noble at the Southlake Town Center (Hwy 114 & Carroll Ave) Monday February 4th at 7PM. Look for me in the tan MySQL baseball hat. If we swamp the Starbucks, we'll move someplace like Joe's Pizza where spirited talk about buffer caches or row locking is not shocking. Well welcome all levels of curiosity and experience.