Tuesday, December 2, 2008

Your help is needed on the MySQL 5.1 Developer Exam

The news of the MySQL 5.1 GA announcement brought many emails asking about the 5.1 exams. First, let me state that if you are studying for any 5.0 exam please keep on with your plans. The 5.1 DBA test is inching closer to alpha test and will not suddenly pop up without warning. It is on the way but most of you will not get to see it until well into 2009. Watch this blog for updates on how the testing is proceeding. Help the dolphinsSecond, the 5.0 exams will not be disappearing anytime soon. Nor will they start 'expiring'.


But I do need help with the new 5.1 Developer exam. The new exam will be part multiple choice and part 'hands on' or performance based testing. Like 5.1 DBA exam mentioned earlier this year, I need the help of some of you to take a look at the new material and judge its relevance. No, getting a peek at this material will not help you with the new exam when you sit for the test. But if you want to help define just what it means to be a MySQL Certified Developer, this is opportunity knocking hard at your door.


So what does it really mean to be a MySQL Certified Developer? Below is a list of what an attendee at a MySQL for Developers class should be able to accomplish at the end of that class. But how many of you hard core coders do all or most of these tasks on a regular basis?

What task can you hand off to your local DBA or do you do both jobs? Are any of these items too simple, too complex (optimization?)? Is anything missing? What do you do to ascertain the skill level of someone who shows up at your office who wants to be a developer?


So put in your views in a response to this posting or send an email to Certification @ MySQL.com with the subject of 'DEV51help'





  • Describe the MySQL client/server architecture
  • Understand the steps necessary to invoke MySQL client programs
  • Utilize the general-purpose mysql client program to run queries and retrieve results
  • Perform queries and analysis of data utilizing the MySQL Query Browser graphical user interface (GUI)
  • Explain the MySQL connectors that provide connectivity for client programs
  • Select the best data type for representing information in MySQL
  • Manage the structural characteristics of your databases
  • Manage the structural characteristics of the tables within your databases
  • Utilize the SELECT statement to retrieve information from database tables
  • Utilize expressions in SQL statements to retrieve more detailed information
  • Utilize SQL statements to modify the contents of database tables
  • Write join expressions in your SQL statements to obtain information from multiple tables
  • Utilize subqueries in your SQL statements
  • Create views utilizing SELECT statements to produce "virtual tables" of specific data
  • Perform bulk data import and export operations
  • Create user defined variables, prepared statements and stored routines
  • Create and manage triggers
  • Use the INFORMATION_SCHEMA database to access metadata
  • Debug MySQL applications
  • Configure and Optimize MySQL

4 comments:

Carsten Pedersen said...

I would tend to agree with all the points, with a few exceptions. Mind, my current role is that of a mixed developer/DBA, so you'll need to take that into account.

Regarding GUI tools: I guess it's important to know that they exist, but as a developer/DBA I don't see any need for knowing about that particular tool. In our shop, we use perhaps 5 different GUI tools to handle different aspects of MySQL.

"Utilize expressions in SQL statements to retrieve more detailed information" -- not sure what is meant by this. "Utilize expressions to narrow down results"? If so, definite "yes".

"Perform bulk operations..." -- I would place a lot of emphasis on csv tables here.

Regarding management of structure, optimizations, configuration: I don't think they are developer tasks. OTOH, the developer does need to understand how/why a DBA may choose to make certain changes, so I do think a basic understanding is required.

Sheeri K. Cabral said...

My thoughts:

# Describe the MySQL client/server architecture
(somewhat important, not hugely so)

# Understand the steps necessary to invoke MySQL client programs
# Utilize the general-purpose mysql client program to run queries and retrieve results
# Perform queries and analysis of data
You add "utilizing the MySQL Query Browser graphical user interface (GUI)" -- I think that's a bonus, but not necessary. However, since SQL can just be typed into a window, even a staunch command-line person can still manage here.

# Explain the MySQL connectors that provide connectivity for client programs

I would go a bit further and have them know some of the features. So many developers are unaware that they can do some really advanced stuff with the connectors (think Connector/J).

# Select the best data type for representing information in MySQL

# Manage the structural characteristics of your databases
# Manage the structural characteristics of the tables within your databases
(I'm unclear what you mean by this, do you mean schema? Or which tables go in which databases? or something more like sharding/partitioning?)

# Utilize the SELECT statement to retrieve information from database tables
# Utilize expressions in SQL statements to retrieve more detailed information
# Utilize SQL statements to modify the contents of database tables

# Write join expressions in your SQL statements to obtain information from multiple tables
Including self-joins, cartesian products (on purpose), etc.

# Utilize subqueries in your SQL statements
But know how to optimize them out if you need to!

# Create views utilizing SELECT statements to produce "virtual tables" of specific data
But know how to see if they're optimal......

# Perform bulk data import and export operations
Sure, maybe even some data transformations. Knowing about the CSV storage engine is useful too.

# Create user defined variables, prepared statements and stored routines
# Create and manage triggers
and events! Don't underestimate the value of periodically updating calculations in a table, instead of using a trigger.

# Use the INFORMATION_SCHEMA database to access metadata

# Debug MySQL applications
I'll go further here. One of the most basic problems that I've had is that developers will say, "the query isn't working," and the first thing I'll say is "have the program print out the query it's trying, and then copy and paste that into commandline". 99% of the time they figure out the problem doing that. But they never ever think to do that beforehand.

It's worse with ORM's, too.

# Configure and Optimize MySQL
The server parameters? Not very important.

I'd add in a few questions about using memcached.

Being able to understand what EXPLAIN means, and using PROCEDURE ANALYSE() to figure out if they have the best data types in place.

Being able to use mysqldumpslow (or better yet, mysqlsla) to find the queries that take the longest.

Using index hints....and how to periodically review to make sure they're still valid.

Using comments in SQL, so that a long query shows up in the processlist as
SELECT /* big report on important stuff */ .......

123 123 said...

Cool article you got here. It would be great to read more concerning that theme. Thnx for posting this information.
Joan Stepsen
Cyprus girls

Anonymous said...

Nice blog as for me. It would be great to read more about this matter.
Thanks for posting that information.
Boris
escorts in Kiev