Which way to go – which way to go…

Was speaking with a friend today about a question they had on how to copy a part of a table including the indexes. It had been a while since I had used those particular quirks of the CREATE statement so I could not recall how to do it off the top of my head (hate when that happens – bell rings but I can’t recall the exact tune.)

Anyway after a bit of quick digging we found CREATE TABLE … SELECT and CREATE TABLE … LIKE. Both can do the job (with a little help), just in different ways.

CREATE TABLE … SELECT will not automatically create any indexes for you, so if you want to have indexes in the created table, you should specify these *before* the SELECT statement. This means you will have to already know what the indexes are and how they are declared before issuing the command – but you can load the table at the same time.

CREATE TABLE … LIKE will create an *empty* table based on the definition of another table, including any column attributes and indexes defined in the original table. To load it, another command will have to be given (INSERT … SELECT).

Oh and before you ask – you can’t use both … syntax error

So which way would you go – and why? Also if you can think of another way to do this (purely SQL) – feel free to educate me.

staying humble

I learn something new every day at work. Some of the stuff I learn is pretty big – but the real humbling ones are the small. A couple of little things that I learned recently:

1) Did you know that NULL and empty string “” can be valid elements of an ENUM column – even if they are not explicitly named!

From the ENUM manual page:

The value may also be the empty string (”) or NULL under certain circumstances:
* If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numerical value 0. More about this later.

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

* If an ENUM column is declared to allow NULL, the NULL value is a legal value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of allowed values.

2) The mysql command line utility can ignore statements except those for the default database named on the command line using the –one-datebase option.

This is can be extremely useful for skipping updates to other databases in the binary log or a backup mysqldump file. Wish I knew about this years ago.

Ex:
shell> mysql -uroot -ppwd --one-database db1 < /path/backup.sql

3) In 5.1 with InnoDB you can reduce DML locking by using the READ_COMMITTED isolation level. Harrison, one of my senior colleagues, even blogged about it.

Until then, I thought that the isolation level dealt only with the reading of data and had nothing to do with the locking of it. My ignorance was exposed to the light and I am still wrapping my brain around it, but I think I have finally gotten used to the feeling now.

Just when I think I have the basics - something little comes along to knock me back into my place. Anyway - back to work!

2009 in review

Wow has another year gone by already. So much has happened that it is hard to think of it all. Lets see if I can recap.

For 2009 I was supposed to try and meet a couple of personal and professional goals. Some of the goals I like to think I have met include:

1) relearning how to balance my work and family. When I started working at MySQL I felt so completely inadequate in my new role that I completely submersed myself in it to try and get myself to someplace that while not comfortable, at least didn’t leave me feeling stupid all the time. I found that place after about a year, but by then I was so consumed with work that I had little time for anything else. 2009 was the year I wanted to take back my personal time.

Now that I have again found that balance, for 2010 I want to get back to my roots and turn my hand back to so PHP coding again. I am massively rusty, but I do miss it so it should be fun.

2) speaking at conferences. I am always telling people to submit talks to conferences but never seemed to have the time to do it myself. Yes I was being a hypocrite.

For 2009 I swore I would submit talks to any conferences I thought I might actually be able to get the time off of work to attend and as luck would have it – they liked me… They really liked me. Crazy people – must be stoned or something.

In 2009 I was accepted to speak at 5 conferences (6 if you include the Microsoft summit) and after being ill before the first one – had a fantastic time. I will definitely be continuing this for 2010.

I have been honored to already be accepted to speak at this years spring conferences – TekX and Confoo. Unfortunately though I have had to withdraw from the Confoo conference due to work requirements, but I will be attending TekX in May. And if I am brave enough, I am also going to try and submit to the MySQL User Conference.

3) install solar panels on the house to reduce our carbon footprint and our electricity bill. Took us a while but it is finally done. The solar panels are in and the solar water heater is working. We are currently waiting for the local electric company to come and install a 2 way metering system.

4) Help my daughter. While not on the original 2009 TODO list, it immediately went to the #1 slot when her father passed this fall. This will be an ongoing thing that may never have an end. But she is worth it.

5) hold semi-regular meetings for the PHPWomen board. Trying to coordinate 7+ people around the globe for meetings is a pain – but we managed to do it a number of times this year to discuss ideas, catch up on projects and touch base with everyone. Not bad.

Ok – time for the listing of things I did not do this year that I wanted to. This list could easily be couple of pages long but we will shoot for a condensed version.

1) get back into writing. I love to write and for some reason, just haven’t had cause to write in a while. Need to contact some people to see if they need any new articles… Wonder if E3 would let me do something for PHPArch maybe…
2) didn’t learn enough for work – still need to get more into performance tuning, benchmarking, cluster and server administration. Always so much to learn, and never enough time (remember trying to balance work/life).
3) PHPWomen – oh let me count the ways… I really wanted to have the paperwork filed for 501c3 this year, never quite made it. Wanted to also have a birthday party for PHPWomen during Zend Con but due to unforeseen circumstances that didn’t happen… also forgot to try and have a “Go Pink” drive for breast cancer.
4) help get the PHPCommunity organized with the provisional board. laying the foundation for hopefully better to come.

This listing can keep going on and on – but now seems like a good time to stop. Here is to hoping 2010 is a great year!

Blog mostly there

I think I have the blog mostly set up and the links working again now (I had forgotten how much I had customized my last theme). I will still be altering the style the next couple days to better reflect my tastes and preferences but that will come with time. For now I think we are up and running.

If you find any bad links/posts please let me know so I can fix them. And tell me what you think of this new theme.

*tap* *tap* *tap* – testing, testing – 1, 2, 3

Umm – is this thing on again? If not I can always ramble a bit to myself – nothing new or different there.

I think I have finally managed to get my blog up and running again (only been about a year) since I keep being told by people that “it doesn’t work” and well … because I really need to get back to blogging. I hate always talking to myself and that seem to be all I have been able to do lately. Much more fun to babble in your ear.

Anyway – lots of stuff going on and will get to that later, but for now – Happy New Year everyone.

Hmm – Guess I can check off “Fix Blog” from my 2009 TODO list (hey be nice I am only a day or so late).

Edit: seems a number of my links are broken right now. I will be fixing them over time.

At PHP|tek next week

Can’t wait for next week and tek. I will get to once again see old and “new” good friends, some I haven’t seen in a year and others I have never met. It has been way too long since we got to sit down and talk face to face. I am also looking forward to making some new friends from the fellow attendees so if you see me be sure to stop me and say hi – if I don’t do it first.

While there I will also be presenting 2 talks on MySQL – one on MySQL server tuning and the other on EXPLAIN. I admit it – I love MySQL optimization. If you do too, be sure to join me though I am admittedly.. kinda upset that I will be opposite Lorna‘s talk on using and understanding open source communities. That was one talk I wanted to attend myself. If I am lucky she will let me look over her slides afterwards

Santa Clara here I come

yep – going to be in Santa Clara next week for some company training. Excited about it since it will be covering areas I am not strong on. Only sucky part is shoving 7 days of training into 5 days.

One year at the Dolphin

[Warning: There is a *lot* of gushing in this post, so feel free to skip it]

Can you believe I have managed to survive my first year at MySQL… and I haven’t gone mad yet… well not much anyway The year has gone by so fast I didn’t realize my anniversary came and went until it was already past.

Anyway, just wanted to post that I still LOVE my job and the people I work with and for. I am constantly learning and questioning and they actually not only like that – they encourage it. Go figure. Admittedly I always feel like a dunce because of it, but I can force my ego to handle it.

This is by far the best job I have ever had. And I have been lucky enough to have some great jobs. Special thanks go out to my “Boss Men” for all they do. You are what keeps my job so awesome – no matter the changes – and I know it.

Ok – time to stop gushing and get myself back to work.

flight for tek09

Just wanted to get the word out that I have finally booked my flight for attending tek and will be arriving in Chicago early in the morning on the 18th and leaving in the evening on the 23rd.

If you are going to be there also and have time on your hands… maybe we can get together for a little bit of sight seeing in the city. I’m game for just about anything though museums, theatre and typical tourist stuff is what I enjoy. Gahh – why did it have to be a week that the cubs are away! I would have paid $100 for good seats to watch them play in Wrigley Field.

Anyway – drop me a line if you want to get together and do something.

Florida Linux Show (post-conf)

I had a good time at the conf and my talk seemed to go over pretty well. I somehow ended up in the largest session room (I admit to feeling a bit overwhelmed). Attendance seemed to be at a decent level, though I think I may have overwhelmed some of them a bit with the information. Had lots of questions at the end and I went slightly over time. I guess the ultimate indicator of how things went is that no one fell asleep during the talk.

As far as I could tell there were about 5 geek girls there… not a particularly large group. Many of the attendees were interested in what we do and how we do it. Only 1 person asked if it was a place for single women to get together (there is always one in the group). After clearing things up a bit with them, they moved on.

All in all – not bad I guess.