End Your If

Tag: performance

  • MySQL: OR versus UNION – which is faster?

    I must say, this came as a shock to me, until I looked at the results and thought about it for a minute.  Before jumping in, let me explain how and where I encountered this.  I was recently working on a project called Deja Scene – The actor to actor movie database.  The purpose of this site is to find two actors who have starred in multiple movies together.

    Because the nature of the algorithm an actor can be either on the left side of the connection or the right side.

    Let’s look at an example, if I search for George Clooney, I will find that he has 372 Deja Scene connections.  Here was the original query I used to retrieve the list of his connections:

    [code]
    SELECT *
    FROM  `actor2_actors`
    WHERE actor1_id =1
    OR actor2_id =1
    [/code]

    Seems pretty straight forward; however, I was seeing really slow performance and I couldn’t figure out why.  I created an index on both the actor1_id and the actor2_id, so I assumed Mysql was properly indexing this.  Boy was I wrong.  Look at the results when I use the EXPLAIN function:

    (more…)

  • Finding That Inner Calm

    I’ve played sports my entire life and I find certain sports for me it’s easier to find that inner calm.  When your head is clear and only exactly what you are about to do matters.  This is always when I’m at my best.  Everything around me slows down, if I’m aiming at something, the target becomes clearer – almost as if I’m pulling the target to me – and there is no way I can miss it.

    I wish I could say this statement is true when I’m writing code like a CASE Statement in SQL.  Oftentimes, my mind is racing with needing to get something done and having a thousand more things to do.  At times it can take me a little while before I realize that I’m actual moving slower because my mind isn’t focused.

    I’ve found a great technique that I’ve learned by playing golf that helps me focus better!

    (more…)

  • CakePHP Version Comparison with PHP Version Comparison

    I’ve seen a few recent blog articles comparing the new version of PHP 5.4 to its predecessors and I thought I should get involved with this a bit as well.

    To perform this test, I will layout the conditions I have chosen.  I’m trying to keep this as simple as possible.  I currently run a Dell Laptop with Windows 7 on it:

    Windows NT 6.1 build 7601 (Unknown Windows version Business Edition Service Pack 1) i586

    Because I often do a lot of .NET development recently I have PHP running as a CGI under IIS 7.5.

    I then created a very simple .NET application that performs 100 requests of the same web page and tracks the response time.  These lists of response times are sorted and the highest and lowest responses are dropped.  The average is then calculated from this.

    (more…)

  • SQL Tips – Why LEFT JOINS are bad

    When you do SQL queries do you find yourself always, or almost always, using left joins?  If you’ve answered yes to this question, I would suggest to continue to read and understand some downfalls that you might be getting yourself into.

    Let’s start by looking at a standard query that has a left join:

    [code]
    SELECT * FROM users u LEFT JOIN countries c ON c.id = u.country_id WHERE u.id = 5
    [/code]

    The following query will return all fields from the users table and all fields from the countries table.  Now you’re thinking, “Yeah, looks good, so what’s wrong with this?”.  Well, I’m glad you asked. (more…)