MySQL variables

Note to self when working with MySQL variables:

@@GLOBAL.var = global level server variable
@@SESSION.var = session level global variable

@var = user defined variable
@@var = session level server variable if it exists – otherwise global level server value… so be sure you understand what you are getting/setting when you use it.

Something new – 1 year later

Time for me to face the music and update what I have been up to – the good, the bad and the ugly.

The Good:
I am back in school doing my prereqs for my masters. Been in school now for 3 semesters and am currently in Calc I. It was harder then I thought it would be to go back to school. Not just for the additional work, but getting back into the mindset and just finding the time for it. Since I am still doing my normal work and presenting at 2-3 conferences a year (rather then cutting back as I originally planned), finding time, energy and will power has been harder then I thought.

The Bad:
Currently I am only taking 1 class a semester. If I want to get this done any time before the second coming, I need to see about bumping that up to 2 per semester and not sure how the hell I will fit that in with everything else and still stay sane.

The Ugly:
Forget reading O’Reilly’s “Doing Data Science” by Cathy O’Neil and Rachel Schutt and learning R. Who the heck has the time for it?

Learning Something New

It has been quite a while since I wrote on my blog and even longer since I had something to consistently write about.

You know how it is, there is only so much time in a day to do things and so many things to do… and work and family have taken priority. But I keep having this ‘itch’ – to stretch a little. Learn something completely new. Something that forces me to grow and play on my computer again just because it fascinates me.

For quite a while now I have been hearing about “Big Data” and “data science”. The idea of taking raw data and looking at it in ways that bring new understanding and knowledge intrigues me.

It reminds me of when I took my GIS class at University. How changing the presentation of data from simple rows and columns and instead placing it “over” a map completely alters how you perceive the data.

I admit, I have been talking a good game up until now, but have not actually done much about it.

So here I am, publicly admitting to the Internet that I am actually going to do something about it.

I will be expecting you to keep me honest about this.

So my plan of action:
1) I have started my paperwork to go back to school for Mathematics. Since my BS is in Computer Information Systems (and not CS) and I remember mostly nothing about mathematics, I have to go all the way back to the beginning (Freshman Algebra) and work my way back up.
* Actionable item: finish my paperwork within 2 weeks so I can be admitted back into school and actually get started.
2) I am currently reading O’Reilly’s “Doing Data Science” by Cathy O’Neil and Rachel Schutt. I at at the end of Chapter 2 and have hit the EDA exercise only to realize I honestly know jack about R.
* Actionable item: Learn at least the fundamentals of R so I can do the simple exercise.
3) Learning the basics of R. Yeah – I luckily bought O’Reilly’s book “Learning R” by Richard Cotton at the same time I bought “Doing Data Science”. Guess I should have started with this book instead.
* Actionable item: to learn the fundamentals of R I will be going through Part 1 of the book. Each chapter in that of course has its own quiz and exercises as well.

I’ll try to keep the blog up to date on how my progress with the books and school go.

Fall Conferences

I am pleased and proud to announce that I have been accepted to speak at the MySQL Connect and Zend Conference this fall.

The MySQL Connect conference is being held Sept 29-30th in San Francisco and I will present on my beginners journey into NoSQLand with MySQL’s NoSQL implementation . Much of the technology for this talk I was not overly familiar with, so it is a lot of fun and very enlightening for me.

This years Zendcon is October 22 – 25th in Santa Clara and I will be presenting on MySQL replication and server variable tuning. Both subjects that I enjoy and plan to update for the latest versions of MySQL.

So if you plan to be at either conference – be sure to find me so we can sit down for a chat and a drink or 2.

updating MySQL statistics

MySQL statistics are used by the optimizer for query plans. Having up to date statistics helps the optimizer “choose” the least expensive way to execute a query.

I know that if I have a query using an index that it should not be and/or performing poorly, I can always run ANALYZE TABLE to update the statistics. But I also wanted to learn when MySQL implicitly updates its internal statistics. So since I can never remember anything – here is a consolidated list taken from a variety of places for me to try and remember things.

  1. 1. ANALYZE TABLE [1]
  2. 2. OPTIMIZE TABLE [2]
  3. 3. When a table is opened [3]. (Don’t forget FLUSH TABLES and FLUSH TABLES WITH READ LOCK)
  4. 4. the mysql client starts if the auto-rehash setting is set on (the default) [3]
  5. 5. Metadata commands (SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES) or the matching INFORMATION_SCHEMA tables/stats [4]
  6. 6. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed [4]
  7. 7. It is implied that some DDL for a table that causes it to be rebuilt can also trigger an update of statistics. (Ex: ALTER, CREATE INDEX, etc.) [1][2][6]
  8. 8. Execute myisamchk –stats_method=method_name –analyze [5]

For more information on how to control the collection of the table statistics see

I am sure I am forgetting other triggers for an update of the table statistics. If anyone knows of any others, please let me know so I can add it to the listing. And if I am incorrect on any of them – be sure to let me know that also.


Jax PHP User Group Presentation

For all the people that were looking for the presentation I gave last night to the Jacksonville PHP user group – I have uploaded it to my presentations page as a PDF. Included are all my speaker notes and references as well.

Oracle ACE

I pleased and proud to announce that I have been accepted to be an Oracle ACE for MySQL.

For those not familiar with it, Oracle ACEs are community members that are nominated and eventually chosen for their (in my case) MySQL community involvement. To learn more about the Oracle ACE program, check out the Oracle page or the FAQ’s.

And just cause I want to show it off a little – my ACE page

A huge thank you to Keith and Dave for submitting my name. I am truly humbled to have been chosen.

Cha, Cha, Changes

As some know I left MySQL Support last week and started working for Kaplan Professional as their MySQL DBA.

To all my old colleagues at MySQL Support – I miss you. You have been my friends, teachers, mentors and co-conspirators. Thank you for everything and don’t be strangers.

To my new colleagues at Kaplan, thank you for the wonderful opportunity you have given me. I look forward to learning and working with you


Man time goes by fast. Only about a week left before Tek. I have to admit to being rather excited about it. I will be giving a tutorial on Optimizing MySQL Essentials that will cover many areas including queries and the server. I also have a regular session called “Why be Normal” that will be an intro into data normalization.

Both of these talks are new for me, so it has been quite a lot of fun putting things together. I hope you will come out and join me and others of the PHP Community at Tek. If you haven’t registered for it yet, there is still time. So get out there!

Tek Speaker Badge

Voices of the Elephpant

Crazy but true – I am on Voices of the Elephpant this week. I had a great time talking with Cal about PHPWomen, so be sure to check it out!

And if you know someone who is one of the unsung members of the PHP Community, be sure to nominate them for an interview at