Ramblings Of A Web Guy
Brian Moon

Altering several things in a MySQL table at one time

To some kick ass DBA or someone that works on MySQL internals, this is probably a no brainer.  But, for just joe schmoe power user types, we wonder about these things.  We could not find the answer on the internet, so I decided to blog about it.  We expected the result we found, but I think it's good information.

Have you ever had to make changes to several indexes or columns in a table?  With Phorum, we keep a series of scripts that run queries to upgrade the database as we make changes.  Recently I had to delete about 12 keys and add about 7 back in their place.  I initially thought to make two sql statements.  One to delete indexes and one to create new ones.  But, Thomas, one of the other developers, wondered if that was really any better.  So, I decided to run some tests.

First I altered a table with 70k rows, dropping one key.  That took 16 seconds.  I then added a key to that same table.  Again, about 16 seconds.  So, I then dropped a key and added a key in one query.  Again 16 seconds.  So, I decided to go for the ultimate challenge.  I dropped 12 keys and added back 12 keys all in one query.  TADA!  16 or so seconds.  The table was an InnoDB table, so I repeated after converting the table to MyISAM.  Again, all the alters took about the same time, regardless of the number of changes.

Update

I was told in the comments that the data was not big enough for a real test.  I hold to the fact that because, as Eric Bergen pointed out, the most time is taken in copying the data, the data size is not that important here.  However, I tried it out on a table with 750k rows just to see.  Dropping 4 keys on a table with 12 keys took 1min 46sec.  Adding 4 keys to the same table, (now with 8 keys) took 1min 52sec.  Adding 4 keys and dropping 4 keys in one alter table took 1min 49sec.  So, looks like I was right.  For fun, I decided to drop all the keys except the primary.  That only took 28sec.  That is what I guessed.  The slow part is copying the table to a new table.  That includes filling in indexes.  It is much faster to write a table with indexes than it is a table with 12 indexes.  However, that was not my use case and not the reason for my testing.  But, it is good information to attach to this blog post.

Null vs. isset()

I am working with a newcomer to PHP and he asked me about setting a variable to null and how to check that.  He had found some example or information that showed that setting a varaible equal to null would unset the variable.  So, he was unclear if he could then reliably check if the variable was equal to null.  Having avoided null like the plague in my years of PHP, I was not sure.  So, I mocked up a quick script to see what the states of a variable are in relation to null. 

<?php



error_reporting(E_ALL);



$null = null;



if($null == null){

  echo "Yes, it is equivilent to null\n";

}



if($null === null){

  echo "Yes, it is null\n";

}



if(isset($null)){

  echo "Yes, it is set\n";

}



if(empty($null)){

  echo "Yes, it is empty\n";

}



?>

The output of this script is: 

Yes, it is equivilent to null

Yes, it is null

Yes, it is empty

It should also be noted that without declaring the variable, you will see PHP notices about an undefined variable.  So, there is a slight difference between and unset variable and a variable that has been set to null.

Wordcraft 0.7 available

I am happy to announce Wordcraft 0.7.  There are two big changes in this release.  On the front end, I added a simple search.  It just uses a LIKE clause.  But, I figure a lot of blogs never reach 1,000 posts.  Even at 10,000 posts, a LIKE would not be too bad.  On the backend, I have switched the post editor to TinyMCE. YUI's editor is decent, but it needs polish.  Perhaps my time with Wordpress just made me more familiar with it.  TinyMCE does save XHTML.  I put some code in the PHP side to use Tidy if it is available to convert it to HTML 4.01, which I prefer.

Also in this release:

  • Fixed an XSS issue in tag.php.
  • Fixing a parsing issue with anchor tags when doing pingbacks.
  • Fixed an error when the remote site can not be contacted.
  • Fix for pingbacks with nice URLs enabled. Was blocking pingbacks.

Seven Things about me - tagged by Brian DeShong

So, my Haystacks teammate Brian DeShong tagged me in his list of seven.  We won
that trivia contest by the way.  It was a real team effort.

So, here goes my seven things:
  1. I have six kids.  Okay, let that sink in.  Yes, six.  Logan(12), Macy(11), Molly(9), Parker(7), Collin(3), and Hudson(6 months).  I know what causes it.  Yes, it is hard at times.  But, there are those moments when you are sitting in the yard or in the den and all is right in the world.  The best program I will ever write will not compare to what have done with my children.  They are truly my greatest project.  My wonderful wife blogs about them at Moonmania.
  2. I started my career as a Visual Basic programmer.  PHP and VB are very much alike.  Neither is OOP, yet people keep trying to make them so.  You can write truly powerful applications if you know what you are doing.  Much of the outside community thinks poorly of the language.  In both cases I was way too busy making cool things and getting stuff done to care or pay attention.
  3. I play disc golf.  It is a great sport.  It reminds me of the Open Source community in that it is a very community driven sport.  The courses are installed and maintained by the players, usually on public land such as parks.  The tournaments are run by local players.  The sport is largely managed by players.  I maintain the web site of one of the larger regional (maybe the largest) tournament series in the world.  I have played in most cities that I have visited including those I go to for conferences.  We have a group of 9 or so people at dealnews that play on a semi-regular basis when the weather is right.
  4. I never graduated college.  The VB work and then the PHP work got in the way.  It is a regret in some ways.  But, I can't tell you what I would have changed about how my life has gone.  Perhaps I will go back and finish my degree at some point.  Sadly, in this world, there are doors that are closed to you no matter how brilliant you are if you don't have a simple piece of paper.  I know people with that piece of paper that ended up working fast food.  But, they could get an interview where I could not.  They would not get the job, but I can't even get in the door.
  5. I have contributed two PHP internals functions.  mysql_fetch_assoc and the now deprecated set_file_buffer.  In the case of mysql_fetch_assoc, I was fixing what was IMO a bug.  mysql_fetch_array originally returned only an associative array.  Someone decided it should return the data in both associative and numeric forms.  mysql_fetch_row already existed to return numeric keys.  So, I simply copied it and added mysql_fetch_assoc.  That was the real wild wild west days of PHP CVS.  You either had full access or you didn't have any access.  I did not keep my development up on PHP so I lost karma on the core later.  My C is not super polished.  Still I will admit I liked being part of the club.
  6. I watch pro wrestling.  There I said it.  Guilty pleasure.  The Tivo makes it easy.  2 hour show only takes about 40 minutes.  Heh, I skip most of the wrestling.  Not everyone is entertaining.  My great grandmother would sit and watch it every Saturday night.  I was hooked from that point.  I won't get going on it.  I would lose you guys.  FWIW, I am a big fan of mixed martial arts too.  That stuff IS real and very exciting.
  7. I am a compulsive code rewriter and/or write it myself kind of guy.  It is something I struggle with every day.  It is why I started Phorum and Wordcraft.  Luckily, the guys I work with at both dealnews and on Phorum are good programmers.  My biggest problem is opening a file I last worked on 5 years ago.  The way we write web applicaitons has evolved so much in the 11 years I have been doing this.  I am using the same language, but have such different ideas.  I can only imagine what the next 11 years will bring.

I will be tagging these seven.  Forgive me if I get a title wrong.
  1. Brian Aker.  The creators of sites like Facebook and Digg may have had some great ideas.  But, without guys like Brian Aker, they would be nowhere.  He makes the things that make dealnews, Facebook and Digg possible.  And he continues to contribute with Drizzle and the new C based gearmand.
  2. John Allspaw.  John manages the operations team for Flickr.  He is a smart guy.  He and I see eye to eye on a lot of web performance topics.
  3. Joe Stump. Joe is the lead architect for Digg.  I have not met Joe in person.  But, I have heard him interviewed and we are on the same page about a lot of things.  I have started using some of his contributed PHP code lately as well.
  4. Alan Kasindorf aka Dormando.  Alan works for operations at Six Apart.  They bought Danga.  They formerly owned Live Journal.  He has kind of become the care taker of memcached, gearmand and all those other cool things that Danga created.  The community is helping him more each day, but he still does a lot of work for those projects.  Including, but not limited to coding.
  5. Jay Pipes.  I think I saw him on someone else's list.  But, I am putting him down anyway.  Jay has been good to me over the years.  It is cool that someone can chair the MySQL Conference, go around talking to user groups and commit code to fix MySQL bugs.  Jay is great because he and I can disagree on just about everything when it comes to programming then toast our beers.  You need people like that in the world.
  6. John Allen.  I work with John at dealnews.  He has learned the search engine optimization and marketing world the way I learned programming.  John posses skills that I don't.  We butt heads on a regular basis about things.  But, the end result is always better than what existed before we started.  I have learned many things from him.
  7. Daniel Beckham.  Daniel is my partner in crime at dealnews.  We have been a two headed monster (yeah, a monster.  Just ask the CEO.) for 8 or so years now.  Unfortunately, Daniel does not blog or twitter or much of anything like that.  So, we likely won't see his seven things.  Many of the things I have blogged about when it comes to building dealnews' architecture were thought of and done by both of us.  I am just a show off extrovert that needs a lot of attention.  So, I do all the blogging and talking at conferences.  
And, I am told I need to post this for my taggies to follow:
  • Link your original tagger(s), and list these rules on your blog.
  • Share seven facts about yourself in the post - some random, some weird.
  • Tag seven people at the end of your post by leaving their names and the links to their blogs.
  • Let them know they’ve been tagged by leaving a comment on their blogs and/or Twitter.

Wordcraft 0.6 available

I am pleased to announce the release of Wordcraft 0.6.  I have been using it for a month or so now and I am learning some things.

I had been having trouble logging in lately from multiple places.  So, instead of trying to work on the built in session handling I had written, I took my own advice (use stuff that exists) and just switched to PHP sessions.  All the cookie stuff is worked out and I can get a lot done with just a little work.  PHP sessions make me a little nervous.  If you have lots of applications installed on the same site that use them, you can get some odd behavior.  But, why reinvent the wheel right?

I have found myself wanting to save a post while working on it.  To do that before, I would have to uncheck the Published box.  To solve this, I changed the behavior of the Save button.  When pressed it now saves the post, but with the published flag set to 0.  This lets you save a post while working on it quickly.  I then added a Publish button to the post form.  The Publish button will save the post with the published flag set to 1.  If a post is already published, you just get an Update button that will save whatever is set in the form.

From a code perspective, I have made all the code use the same array for user data.  I had a separate one for the core, one for the template and one for the admin before.  That was getting complicated.  So, they all just use the same one now.

The last thing I did was add meta refresh tags to the admin success pages.  I like having a success page to tell me something worked.  But, I really want it to move along after it is done.  It does that now.  It is set to 3 seconds.  I may knock that down a bit.  That 3 seconds starts after the page is fully loaded.  So, it can be more like 5 or 6 if stuff has to load.

OSCON moves to San Jose - or - I will miss Portland

I have had the privilege to go to the O'Reilly Open Source Convention 4 of the past 5 years.  During that time, it has been held in beautiful Portland, Oregon.  Well, the OSCON 2009 web site is up and they are moving it to San Jose, CA.  I have never been to the San Jose Convention Center.  The pictures look nice.  I have only been to San Jose at all to get off a plane and go to the Santa Clara Convention Center.  I hope San Jose has more to offer than Santa Clara.  The Santa Clara Convention Center is nice.  And the hotel it is attached to is also quite nice.  I have attended an Apachecon in 2001 and two MySQL Conferences (2007 and 2008) there.  But, outside of the convention center, there is not a lot of fun stuff to do.  You have to travel a good distance to find good medium to high end restaurants.  It is in the middle of a lot of large office buildings with names like Yahoo!, Intel, AMD, nVidia, etc. on them.  The other space is taken up by apartments.  But, I did not start this post to dig on Santa Clara.

I will miss Portland.  It is a neat town.  Lots of good food.  Lots of good beer.  Easy to navigate once you get used to the bridges.  If you like public transport it is quite accesible.  There is good disc golf in the area too.  That was always a plus.

But, here is to new beginnings in San Jose.  May it be as fun and educational as Portland has been these past few years.

Know PHP/MySQL and wanna earn a buck?

Apparently, all the work we have put into dealnews.com over the last 11 1/2 years can be had for $250 and can be delivered in 10 hours.

http://www.getafreelancer.com/projects/PHP-Joomla/Clone-dealnews-com-CMS.html

I see it was canceled.  That is smart.  What we do can not be done for that kind of money.  $350?  Maybe. =)

Speaking of dealnews, we have been rated the #1 Black Friday site by PC Magazine.

Open Source Web Design

So, my wife told me that my site design was boring.  Yeah, she was right.  I am no designer.  I just don't have that gene.  But, during my work on Wordcraft, I came across some cool places to find designs that are relased under Open Source licenses.
  • Open Designs - This is arguably the the prettiest of the three. The search, however, is painfully slow because all results return on one page.  I guess if you can wait, this is a plus as browsing is easier.  Also, you can pick multiple colors and choose by license.  They only list XHTML templates (at least as search options).  That could be a turn off if you like HTML 4 like me.
  • Open Web Design - The site itself could use a design overhaul.  But, the content is good.  The search lets you choose primary and secondary color, a unique feature among these sites.  Thumbnails are a bit small though.
  • Open Source Web Design - Their search is not as powerful as the others, but it does return very fast.  The thumbnails are a nice size.
You will find the same content on all three sometimes.  But, it comes down to browsing and searching.

I found my new design at one of those.  Not sure which, I looked at a lot of them.  I did not use the template's HTML exactly as I like HTML 4.0 and wanted a different sidebar than the original author.  But, the design is the hard part.  So, thanks for Deep Red.

Wordcraft 0.5 available

Well, I blogged about Wordcraft the other day.  I have just been running live on the software for 4 days now.  Well, that post had no URI associated with it.  It took me two days to figure this out.  Oops.  Welcome to eating my own dog food.  So, running this live with actual users (and a host of bot spam attempts) I am learning a lot and making a lot of commits.  So, I may very well roll once or twice a week for the first few weeks.

So, with that, I have packaged 0.5.  There are 15 changes in this package.  Some features, but mostly bug fixes.  So, if you could use a simple blog, give it a try and help me debug it.  If you do, please use the Google Code issue tracker.  Maybe I can figure out how to have those things emailed to me.

Wordcraft, a simple PHP blogging application

So, a while back, not sure when, I was listening to the P3 Podcast and Paul mentioned his dislike for Wordpress.  He said he wished there was a simple blogging application.  I am probably misquoting him horribly.  It was an idea that I had been tinkering with.  So, I started on Wordcraft in my spare time.  Like super spare time.  That time between the kids going to bed and me falling  asleep.  So, it took a while to get it to a usable state.

Up until now, I have used Wordpress.com for my blogging.  It works quite well.  You can get started quite quickly and it does what most people need.  My wife uses Blogger for our family blog.  It is, IMO, not as nice as Wordpress.com in some ways.  But, it does allow you to edit your styles (for free) and such which is nice.

So, why would I want to reinvent the wheel?  I am a control freak and rarely run other people's code.  I know, it is a character flaw.  I am working on it.  So, what did I come up with?

I had some goals when I started on this.

  1. Keep it simple.
  2. Focus on what I am good at doing.

Keeping it simple

I use MySQL.  I didn't try to make it work with every possible database.  In fact, it only uses the mysqli PHP extenstion.  The few objects (CAPTCHA) are all PHP 5 objects.  I don't plan to worry about PHP 4.  The templates don't use a template language.  They use plain old PHP.  The are scoped to protect template authors from global scope.  There are only 6 files required to make a new template.  There are just 589 lines of code in the forward facing scripts.  The admin has 2,446.

What am I good at doing?

I write PHP/MySQL code that has to work fast for a living.  It is what I get paid to do.  I am not a designer.  I am not a spam catching wizard.  I don't write cool javascript widgets.  So, I focused on the PHP/MySQL parts of the code.  For templates, I used designs that are released under the Creative Commons license.  I use Akismet and the CAPTCHA libraries from Phorum for spam catching.  I used the YUI Rich Editor for the admin where I needed a WYSIWYG widget.  I even link to the YUI sources that are hosted by Yahoo.  No sense taking on that bandwidth or storage.

So, what does it do you ask?  Well, here are some of the features:

  • WYSIWYG editing via YUI.
  • Comments with optional CAPTCHA and/or Akismet.
  • Custom pages can be created.
  • Tagging of posts
  • Custom publish dates
  • Automatic Pingback support
  • Friendly URL support with mod_rewrite
  • 5 Templates in first release.  Easy to build more.
  • Email notifications to authors

There are some things missing of course.  Internationalization of both the admin and templates is a big one.  There is no current search engine for blog posts.  There is no "blog roll" type of feature.  There is no date based archive.  And I am sure there is more missing.  And I am sure there are bugs.

But, if you would like to try out yet another PHP application, I welcome you to give it a try.  The code is hosted at Google Code.  It is a BSD licensed application.