Search Speed: Isset vs In_array vs MySQL Query

As databases and visitors grow, sometimes ‘old code’ doesn’t work like it use to: it takes too long or times out for exceeding the maximum time allowed by your server for PHP execution. On a MySQL table with 30,000 rows (not too many), the query was checking one column for <3 and then matching a long list (10,000) of values in a second column using a long WHERE column2=’value’ clause. Here is an example of the table:

|--column1--|--column2--|
|     0     |    1234   |
|     3     |    5678   |
-------------------------

A MySQL query took a while, but didn’t time out when the list of WHERE column2=’value’ was less than 7000, but it was just overwhelmed at 10,000. Okay, fine, time to go to phase two: in_array().
There was an existing array of values for column2, so I could simply pull a list of <3 from MySQL (quick) and then loop through (foreach) each of the results and check whether they are in_array() for the original source. If they were, just save them to a brand new array that I’ll use to work further through the problem. For clarification, that means that the system should go through 10,000+ iterations and check if each value is in_array(). If you had not already guessed, this was disastrous. In_Array() is very inefficient, and if you are dealing with an array that is more than 100 or so values, you’ll quickly learn this fact. So what to do? Use isset().
Isset(), as a function, is incredibly fast. It only checks whether something exists, and is a great way to check for whether something exists in an array. However, the trick is using isset() will look at the KEYS, while in_array() will look at the VALUES. Is that problem? Not if you build the original array as [value]=>value (set the KEY and VALUE the same). Now, as long as your values are valid keys and unique, this won’t be a problem. Then, you can use a foreach loop to check whether it is found in the original array. Here is some sample code:

$array_source = array('abc'=>'abc','def'=>'def','ghi'=>'ghi');
$array_results = array('xyz','ghi');
foreach ($array_results as $v){
  if (isset($array_source[$v])){ //fast
    $result[] = $v; //if found, save it to a fresh array
  }
}

What was the final result? The query, run 6 different times for 6 different data sets, went from a total execution time exceeding 2 minutes (MySQL), to not working at all (in_array), to 0.18 seconds. Yes, less than one second! That is the power for checking whether a value is set when comparing arrays. Isset() is even faster by a notable amount when compared to array_diff().

MySQL Find Fields in Table not Found in First Table

It took a ton of googling, and it was really hard to find the answer. I had two tables, and wanted to do a MySQL Query that seems like a NOT IN between two tables. There are two tables with one unique field that is the same between the tables. The goal is to find the rows in the second table that are not found in the first table based upon the similar field.

I could attempt to explain this, but someone has already done a very good job of this, so I’d rather just provide a link to their content. The post is from the author of “High Performance MySQL”, Baron Schwartz, and covers writing an SQL Exclusion Join. The key area of interest is the section on LEFT OUTER joins. He offers this example query:

SELECT apples.Variety
FROM apples
    LEFT OUTER JOIN oranges
        ON apples.Price = oranges.Price
WHERE oranges.Price IS NULL

When in doubt, find someone smarter to answer the question for you. 🙂 Thanks Baron!

Seven Downloads for Young Web Developers

There are tons of books and tutorials on PHP, MySQL, Zend, and SEO on store shelves. There are even more resources found only on the internet. Sometimes you need something in the middle, content you can download legally and keep handy when you are offline. Below are a few completely free resources that most beginner or intermediate web developers (even if you do it just for yourself) will find useful.

PHP

1) PHP Reference: Beginner to Intermediate PHP5

Shameless self-promotion. A reference for many of the functions within PHP that serves as a quick go-to resource for checking syntax and remembering the nuances of many of the functions. It is available as a PHP book you can purchase in print, however the entire book is released under creative commons and available as a PHP reference PDF.

2) Object Oriented PHP Tutorial in PDF

Provided by killerphp.com and Stefan Mischook, this is a PDF version of his article on the topic of object orientated programming in PHP. It gives a conversational explanation to the basics. More information and the OOP PHP PDF is available over on killerphp.com.

3) Zend Framework: Surviving the Deep End

The Zend Framework can help developers organize and write more efficient PHP code for large projects and has become one of the top frameworks used online today. From the page…

“The book was written to guide readers through the metaphorical ‘Deep End’. It’s the place you find yourself in when you complete a few tutorials and scan through the Reference Guide, where you are buried in knowledge up to your neck but without a clue about how to bind it all together effectively into an application.”

While available online, there is a link to downloading the PDF version in the bottom right. Check out the Zend Framework survival guide.

HTML, CSS, AJAX

4) The Woork Handbook

Another compilation of online articles compiled and organized as an offline document. From the page…

“The Woork Handbook is a free eBook about CSS, HTML, Ajax, web programming, Mootools, Scriptaculous and other topics about web design… directly from Woork!”

This isn’t a full study of any single topic, but is filled with tidbits. Grab the Woork Handbook.

5) Added Bytes Cheat Sheets (formerly ILoveJackDaniels)

While the site’s name has changed, the great resources have not. Cheat sheets are designed to cram (in a useful way) tons of information into the front and back of an 8.5″ x 11″ sheet of paper. There is little excuse for not keeping these handy. Grab the cheat sheets for HTML, CSS, RegEx, Mod_Rewrite, and more.

MySQL

6) MySQL Manual

All MySQL documentation is available as a downloadable file. Choose from various options for the MySQL documentation.
Editor’s Note: I had trouble finding a good, free, legal resource for MySQL that wasn’t hyper specific and was user friendly. I hope others have suggestions they can leave in the comments.

SEO (Search Engine Optimization)

7) Beginner’s Guide to Search Engine Optimization

SEOMoz.org is an amazing website for learning the complex world of SEO with a very clear, user-friendly tone. Besides that, they offer a bunch of tools for helping your new website get better in the eyes of search engines (aka Google). Available as HTML, MS Word, or an OpenOffice document, you can get a copy of the Beginner’s Guide to Search Engine Optimization over on SEOMoz.org.

mysql_real_escape_string() vs addslashes()

(pgs. 124-125)

When describing the function mysql_real_escape_string(), the following note was included:

Note: Performs the same functionality as addslashes().

While they are practically identical in their behavior, this is an oversimplification of the extra strength of mysql_real_escape_string(). At this point, I will pass on the following blog post that provides some extra insight into the comparison of the two variables:addslashes() Versus mysql_real_escape_string() by Chris Shiflett. I was not previously aware of this difference and security loophole.