The Importance of EXPLAIN and SQL_NO_CACHE in Query Development

Posted October 7th, 2009 at 23:17 CST in SQL | Tags: , , | Leave a Comment »

On average, I guess 35% of my over time at work involves MySQL query building, optimizing and re-writing. I find it an enjoyable challenge, and recently I re-worked an existing framework for a new site which put my focus almost solely on SQL, queries, and the schema.

With a normalized database schema, SQL optimization tends to focus on aspects outside the query, like making sure indexes get put in the currect locations. But I happen to be stuck with large and slightly-non-normalized tables. So, in order to write queries against these tables for a site that averages 30k hits per month, I need to be able to see what MySQL is doing with each query. For this I would be lost without EXPLAIN and SQL_NO_CACHE.

I don’t like writing tutorials, and I don't really have time. But I love linking to great documentation. Most of what I know about MySQL I learned from their documentation, and I encourage even experienced query writers to make sure they know the importance of testing query times with SQL_NO_CACHE or using a development environment with caching completely disabled. When it comes to web applications, even microseconds can hurt in the long run.

Leave a Comment »

PHP is Lame, 5 Reasons Why

Posted September 29th, 2009 at 00:50 CST in Uncategorized | 5 Comments »

I've been working at KASA Capital for about 7 months and I feel like I've gotten a good handle of some of the more “gritty” details of how PHP works. Unfortunately though, it means I have a running list of annoyances with the language—some more difficult to deal with than others.

  1. Keywords are case-insensitive. Being able to write the word true using uppercase or lowercase may be aestheticly useful depending on the context, but I don't think there would be any situation where writting tRuE or TRue would be a good idea. The last thing I want to see when optimizing someone else's code is variable-case keywords. Even using all uppercase keywords is highly suspicious, and when I actually saw this the other day, I found the code almost unreadable. I also think that having case-sensitive keywords in PHP would improve parser times, but I haven't tested this theory. What I do know is that other interpreted languages like Python have strict rules for keywords, and Python code tends to run much faster than PHP code (of course this is not a proof though and may actually have no correlation).

  2. isset(), unset(), and NULL are confusing. A while back, I wrote a whole bunch of Python code. Transitioning back to PHP for work was difficult first because of the semicolon use in PHP, but second because of PHP’s (lack of) variable declaration. Technically, the documentation is very specific, but most implementations have a low setting for the default error_reporting level so many programmers have learned to be lazy with variables. After a security audit of your site though, you would probably learn very quickly to code using error_reporting(E_ALL|E_STRICT).

  3. When using double quotes with a string, you may include PHP variables in it thus having PHP parse the string. This really isn't that big of an issue since it is well documented, but most PHP programmers I've run into don't understand that calling the parser takes additional resources which may in turn slow down your code.

  4. Magic Quotes. If you have ever had to deal with magic quotes you will understand that this is the single most terrible aspect of the language to ever happen. The theory of it might have been okay, but in practice it is extremely frustrating--not to mention that against recommendations, debian (and thus Ubuntu) packages of PHP have magic quotes on by default. I am so glad this was deprecated in 5.3.

  5. I can't echo HTTP headers. The only way to manipulate HTTP headers is to use the header function. Under most circumstances this is okay, but there are a unique instances like streaming content in a proxy-like manner where the headers are important and it would be too intensive to parse the stream, so I would like to simply be able to echo them. When using PHP in CLI mode, I can use the -q flag to turn header output off, but this is not available in CGI mode, which most people use.

Even with all that, I do still use PHP at work; but constantly trying to write clean, efficient code goes a long way.

5 Comments »

Apology to Users of Internet Explorer version 6

Posted September 21st, 2009 at 00:08 CST in Uncategorized | 1 Comment »

This is my apology to users of Internet Explorer version 6. If you are viewing this site in that browser, you probably think that I am a terrible web designer. The real reason my site looks terrible (that is the side bar is at the top and is not a set width) in Internet Explorer version 6 (and earlier) is that the rendering engine of that browser is antiquated and does not recognize CSS commands margin:0 auto; or max-width and nor does it correctly render float, all of which every other modern browser including Internet Explorer version 7 displays correctly.

I don’t understand why you would freely choose to use Internet Explorer at all, but if you are, I beg you to take a look at one of the better options: Mozilla Firefox, Google Chrome. Of course if you are an Apple user, Internet Explorer hasn’t been an option for a few years.

In case you just think me lazy: my full-time job is writing PHP, XHTML, Javascript and SQL, so when I get home, I don't spend that much time coding. When I built this theme, I only spent a few minutes on it here and there; so I actually just forgot to test it in really old browsers. But yet again, I feel less-than pleased with the theme, so hopefully on the next rendition I will get around to fixing the site in Internet explorer 6.

1 Comment »

Hey Look! A Wordpress Widget

Posted September 20th, 2009 at 12:56 CST in TinyFeed | Leave a Comment »

If you frequent my blog, you may have noticed a page titled “TinyFeed” which describes how I am working on a Wordpress widget, and how I currently use it. I finally finished and published it. If you use a microblogging service and use wordpress, you probably want this widget.

Leave a Comment »

Au Revoir Verizon

Posted September 2nd, 2009 at 10:46 CST in Uncategorized | Leave a Comment »

I have been a loyal customer with Verizon Wireless for about 4 years for a simple reason: they have better coverage than anyone else. This well-advertised fact is not disputed by other companies, and I can personally attest to it, having lived in North Dakota for a 5 years.

But they are behind the times and just don't understand customers. When I buy a phone with Bluetooth, I expect to be able to use that functionality. No. With my Bluetooth-capable phone on Verizon, they would not allow me to transfer pictures off my phone via Bluetooth.

So after sampling a bunch of phones, I decided on the Pre with Sprint. I will be saving about $30 per month over comparable Verizon plans. (I don't understand why their plans are so expensive either.) So far both my wife and I love our new phones, and soon I hope to figure out how to write blog posts from my phone.

So at last, au revoir Verizon.

Leave a Comment »

Page Load Times Are Very Important

Posted August 22nd, 2009 at 14:00 CST in Web Development | Tags: , , | Leave a Comment »

We've progressed pretty far since 56k dial-up days; no more waiting for pages to load in order to access information, almost everything is immediately accessible via multi-megabyte per second internet connections. But we would be naive to think that there have not been growing pains. In the IT industry, high turnover is both frequent and expected due to the constantly changing environment. (The exception is highly specialized fields.) Improving technologies changes the needs of the people and their expectations.

Reading Percona's Web Scaling Blog, I saw a page on Front End Performance which outlined why web developers need to be conscious of users.

Web Site Speed Affects your bottom line. [...] Amazon’s experiment: Slowing pages by 100ms decreased sales by 1%

Highly-available internet access shifted our needs to security and privacy, but now we expect websites to be uber-fast. As a web developer, I need to listen to the needs of the user. The studies presented here show that those who do not develop sites up to user expectations will not get the traffic and revenue they desire.

This argument is very similar to the business scenario of the credit card purchase. Using a credit card is convenient to users, and even though it presents cost to a business to accept credit card purchases, if they do not accept them, they will lose business because customers expect it. Also, if a business abuses the power of the credit card by throwing in additional charges that are unnecessary (or unethical) customers will either not make purchases or not return for additional business.

So while producing a high-performance website—or trimming an existing one—may be costly, it is an important gesture, and will probably pay off.

Leave a Comment »

Google Chrome’s Application Shortcuts Now for Linux

Posted August 20th, 2009 at 21:03 CST in Uncategorized | Tags: , , | 1 Comment »

When Chrome was first introduced, one of the neat little features was the ability to use the browser to directly launch web apps from your computer's desktop. This feature was called “Application Shortcuts” and can be accessed from the Page menu.

At first—and for a long time after that—I didn't see the point. For example, launching Gmail in such a way still results in launching an instance of the browser, and if you are a cookie fanatic, you've noticed that the app window shares its session with other launched windows.

Today decided to take a second look at Application Shortcuts when I noticed the menu option mission from Google Chrome on Ubuntu, and surprisingly discovered they might actually be useful. With the example of Gmail, there is no native desktop application for Gmail so my options are either to use a web browser or some other desktop email client like Evolution, which I hate using since it ties me down to a single computer. Running Gmail as an application through Chrome surpasses this barrier and gives me more flexibility with my web browsing.

Getting to the entire point of this post though: Application Shortcuts are now available for the Linux build of Chromium. When I noticed this feature was missing I hopped onto IRC channel #chromium and asked about it. A nice developer promptly responded saying it was added to the SVN trunk a couple days ago. Sure enough, I ran updates and there it was. So, I tried it out. It's not the best thing in the world, but Gmail is a lot better ran as a chromium application.

1 Comment »

Firefox Woes

Posted August 20th, 2009 at 09:03 CST in Uncategorized | Leave a Comment »

I've previously talked about my switch to using Google Chrome and my primary web browser, and for a few months of almost constant use—even to type this post—I have enjoyed it. Chrome's speed increase over Firefox not only relieves frustrations, but it increases my productivity. Pages load faster, a DOM viewer and Javascript inspector is built into the browser, it is open source, and there is a decently stable release branch for Ubuntu. And, within the last few weeks plugins were available for it in Ubuntu, so Flash works now.

But while all that sounds awesome, I unfortunately cannot remove Firefox yet. Two reasons:

  • Using Firebug in Firefox is the only way I know of to view the response of an AJAX call without hard-coding in outputting the response via an alert() or something. It is very frustrating, but Chrome's document inspector only tells you where an AJAX call was made to, not whether it succeeded or failed, or what the response was. This is crucial to debugging web apps.
  • Firefox is a mature project and very, very rarely has any problems. It's true that Firefox is indeed a tad bit slower at just about everything, but I almost never have problems with it and often have to switch to open up Firefox because Chrome has crashed or is behaving awkwardly.

At work I use Firefox 3.5 on Windows, and that seems to almost keep up with Chrome, so once Karmic (Ubuntu 9.10) is released, I'll have to give it another try.

Leave a Comment »

Love & Hate: HTTP Cookies

Posted August 13th, 2009 at 00:44 CST in Uncategorized | Tags: , | Leave a Comment »

Ever since I understood what HTTP cookies were I've hated them. I do not like websites saving content on my computer without me explicitly approving. I feel like there should be alternative methods of storing persistent client data. From the start cookies have posed security issues since it is fairly trivial (for my perspective) to steal someone else’s cookie by packet sniffing.

Nowadays a site can specify to use cookies over SSL, but even then, a site can choose not to and offer HTTP cookies over an HTTPS connection. Of course, because cookies do store data on the client and because of their implementation in browsers, a hacker might be able to steal cookie information using malicious Javascript.

Some have proposed alternative methods to using cookies to store persistent client data: hidden form fields, clever URLs passing tokens as REST queries, embedding a flash object, or even using a hidden iframe and writing ajax response text to it. But I feel like all of these proposed solutions only raise more issues.

So then left with no alternatives, I have chosen to embrace cookies when necessary and to not implement them whenever I can get around it. The specific case of having a shopping cart on a website seems to have only one good solution: storing a session ID in a cookie and send that to the server where the actual client data is stored.

Approaching the problem from a different perspective though, when a person uses a web browser, that person is given the option of turning off automatically accepting cookies, or even just rejecting third-party cookies—which I choose to do when I browse. So then there it is: I also love HTTP cookies because I enjoy using websites like Gmail which require cookies for advanced features.

Leave a Comment »

Stolen Content On the Internet

Posted August 11th, 2009 at 20:45 CST in Uncategorized | Leave a Comment »

When I was in college I took a class called “Church Law & Taxes” and for the most part it was supremely boring, but a few topics stood out that I really enjoyed such as copyright laws in irregular situations like sermons. As the Internet has grown, so has popularity in many different aspects of copyrights and piracy. Many bloggers do not concern themselves with this topic as their blogs don't get enough traffic to really matter, but it is a big deal. Many companies go out of their way to try to make sure their content is used only by them as their content may play a crucial role in making money. I read an article recently describing what to do about potential copyright infringement on the Internet—interesting stuff.

Other websites take a different approach: xkcd licenses it's content under a creative commons license, and other sites like news web sites offer the majority of their material free of charge and sell advertising. It's difficult to know which avenue to choose when publishing content on the Internet, but I find the law aspects of it fascinating, and have been accused of being a “license junkie” because of my knowledge of open source licenses. Personally, I have chosen to retain all rights to my content—at least for now.

Leave a Comment »

This work by Joseph Piché is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.
Proudly powered by WordPress.