I’ve always been a big proponent of data modeling and using modeling tools to create physical database designs (as an aside, I’ve never been much on logical modeling), so I’m naturally happy about our upcoming MySQL Workbench tool.  During my DBA years, I basically lived in my modeling tools and used them all the time for many different purposes.  I’ve just written a new dev zone article that talks about why modeling is important for creating high-performance databases, and how you can use modeling tools for change management/control purposes and how doing this can keep you from getting burned when DB changes go bad.  Check out the article and let me know what you think.

After seeing some very good "of-the-day" series, I thought why should MySQL variables be left behind. So, my contribution in the race - "variable's day out". I won't brand it "of-the-day" series as it calls for too much of dedication and given my track record I shouldn't promise that. Henceforth, instead of calling a variable lucky enough for a day, lets call a day lucky enough for a variable. Though I will try my best to keep the lucky days rolling.

Today's variable is delay_key_write. Properties:

Since ages, I was busy with many projects including my marriage. Nice to be back on the blog with an update on MyEye - the project I announced in my very first post.

MyEye is an (or is going to be an) open source monitoring tool for MySQL installations with handy knowledge-base and advisories for MySQL DBAs.

So, I'm done with the first round of analysis and design for MyEye. As far as it is planned, it's going to be a framework that would provide interface for people to write their own monitoring rules. Meanwhile MyEye web site is also under construction and I will be publishing the documentation soon there.

Will be looking forward to your contributions for creating an open knowledge base for MyEye.

Today’s question comes courtesy of Diego Medina.

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

When using a 1-byte character set like Latin-1, a VARCHAR(250) field requires 251 bytes of storage. A VARCHAR(260) field requires 262 bytes of storage. Why?
(more…)

If you want to filter a query for further usage, you need first to identify the query as the one to process, and then you need to isolate the elements to use for the task.

Both needs can be satisfied using the Lua string.match(string,pattern) function. Since Lua is an object orieneted language, this function can also be used as stringvar:match(pattern).

Let's see an example. We need to do something with with the UPDATE statements to the employees table.

function read_query( packet )
   if packet:byte() ~= proxy.COM_QUERY then
       return
   end
   local query = packet:sub(2)
   local cmd, table_name = query:match('^(%w+)%s+(%w+)')
   if cmd and cmd:lower() == 'update' then
       print( 'updating table ' .. table_name)
       if table_name == …

I woke up this morning to a nice surprise. Peter Zaitsev from MySQL Performance Blog mentioned my fix for a crash in MySQL. Thanks Peter! That reminded me that I need to write this post about another fix.

A storage engine is called a “handler” in MySQL internals. Handler events are per row at the storage engine layer. For example an insert query that inserts 3 rows will cause one Com_insert increment and 3 handler_write increments. The way these statistics are implemented the storage engine is responsible for incrementing statistics itself. Innodb is missing function calls to increment the handler_delete and handler_update status variables. I’ve filed …

On March 4th I wrote about an observation we made concerning InnoDB locking behavior in combination with foreign key constraints. Since then we have been in contact with MySQL support and they in turn with the InnoDB developers. One of the conclusion I can already draw from the whole thing is that another level of indirection (InnoDB being a part of Oracle, i. e. an external resource to MySQL,

To be really successful at working with databases, there are many different disciplines to become proficient in - things like disaster recovery, security management, data integration, and more. But there are less than a handful of things you want to be really good at; becoming super-skilled in them will help you enormously in your database career. And believe it or not, one of these key areas is data modeling and its kissing cousin, physical database design.

In reviewing some of my notes I thought it was relevant to ensure this small MySQL gem is documented for my history purposes.

Historically I’ve seen people do a complex SELECT statement twice, first to get a COUNT(*) and then to retrieve the data, perhaps in a paginated format. In MySQL you can combined both SELECT statements into 1 SQL. You still need a second SQL statement, but it is less impactful to your system.

Here is how?

mysql> select sql_calc_found_rows * from mysql.user limit 2;
...
2 rows in set (0.01 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)