MySQL: OR versus UNION Ė which is faster?

Published on Mar 24, 2014 by Jamie Munro

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:



SELECT *

FROM† `actor2_actors`

WHERE actor1_id =1

OR actor2_id =1



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:

Read more >

Tags: SQL | Optimization | performance | mysql

Finding That Inner Calm

Published on Jun 18, 2013 by Jamie Munro

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.† 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!

Read more >

Tags: performance | stress | Theory | organization | Rants

CakePHP Version Comparison with PHP Version Comparison

Published on Jun 25, 2012 by Jamie Munro

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.

Read more >

Tags: CakePHP | Optimization | performance | php | cakephp 2.0 | PHP

SQL Tips - Why LEFT JOINS are bad

Published on Feb 13, 2009 by Jamie Munro

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:

SELECT * FROM users u†LEFT JOIN countries c ON c.id = u.country_id WHERE u.id = 5

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.

Read more >

Tags: SQL | Optimization | left joins | inner joins | performance