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

Setting Up CakePHP with IIS

Published on Jun 11, 2013 by Jamie Munro

I’ve found myself doing this several times over the past little while as I had to switch computers.  Each time I would struggle with a few of the steps, so I thought I would document them this time and share for future me (or you if you like).

Read more >

Tags: CakePHP | php | PHP | mysql | iis

Mysql: Insert or Update in One SQL Statement

Published on Oct 3, 2012 by Jamie Munro

I must say, I really wish I knew about this technique years ago – before I started using frameworks to do a lot of my development.  The effort that was wasted in building separate code and separate logic to deal with inserts vs updates, when all of this time, there is a nice and simple feature built-in to Mysql that does this for us…

Read more >

Tags: SQL | mysql | insert | update | duplicate

Regular Expressions with Mysql

Published on Oct 2, 2012 by Jamie Munro

I must admit, this is something that I've never done until just recently; creating a regular expression in Mysql.  I've typically been able to accomplish whatever I wanted to with a LIKE statement or some server-side code.

However, just recently I updated the plugin I use to output sample code.  I was previously using a syntax of [codesyntax lang="php"] and now I needed to change it simply be [code].  This wasn't that simple since I've done hundreds of blogs with several different languages.  In searching I had over 15 distinct differences – hence, regular expressions to the rescue!

Read more >

Tags: SQL | mysql | udf | regexp

Mysql Uptime Check Script

Published on Oct 1, 2012 by Jamie Munro

I've been pretty vocal recently over my move to Amazon EC2 and switching to LEMP stack and then switch to a LEPP stack.  This has certainly seen some great improvements in speed, traffic, and Google indexing.  Unfortunately as many of you may know who run their own dedicated server, it's not all white sandy beaches and beautiful sunsets!  When something goes wrong, it's up to you to fix it.

So far the only issue that I see keep cropping up is my Mysql database seems to crash every several days.  From what I can see, it appears to be a lack of memory issue – it appears my EC2 server is just shy on a bit of memory.

At this point, I don't think it's worth upgrading; instead I've built a simple Mysql uptime script that will ensure it's always running for me.

Read more >

Tags: SQL | Theory | mysql | mysqladmin | crontab | uptime check

Renaming a Database in Mysql

Published on Sep 28, 2012 by Jamie Munro

Load Testing Mysql with mysqlslap

Published on Sep 17, 2012 by Jamie Munro