Posts Tagged "mysql"

On the Importance of Documentation Updates

Published on September 20, 2012

Even though the site aggravated me at first, I still occasionally troll Stack Overflow. One of the leading problems I see in questions pertaining to PHP & MySQL, is people’s use of the MySQL extension in PHP. This extension, it turns out, is being deprecated. But does the documentation reflect this fact? Yes and no.

Certain function pages, such as mysql_real_escape_string, have big red boxes at the top indicating that the extension is being deprecated. “Don’t use this”, they seem to shout. Other function pages, however, such as the mysql_result page, don’t have these warnings. Likewise, the top-level MySQL Drivers and Plugins page lists the MySQL extension first, with no indication whatsoever that the extension is being deprecated.

At the very least, every single documentation page that deals with the MySQL extension in any form or fashion, needs to include information about its intended deprecation. Otherwise, thousands upon thousands of programmers will write code using a plugin that is quickly nearing it’s end-of-life. Which, based on what I see at Stack Overflow, already seems to be the case.

MySQL and Localhost Performance

Published on April 5, 2011

I ran into an interesting phenomenon with PHP and MySQL this morning while working on a web application I’ve been developing at work. Late last week, I noted that page loads in this application had gotten noticeably slower. With the help of Firebug, I was able to determine that a 1-second delay was consistently showing up on each PHP page load. Digging a little deeper, it became clear that the delay was a result of a change I recently made to the application’s MySQL connection logic.

Previously, I was using the IP address 127.0.0.1 as the connection host for the MySQL server:

$db = new mysqli("127.0.0.1", "myUserName", "myPassword", "myDatabase");

I recently changed the string to localhost (for reasons I don’t recall):

$db = new mysqli("localhost", "myUserName", "myPassword", "myDatabase");

This change yielded the aforementioned 1-second delay. But why? The hostname localhost simply resolves to 127.0.0.1, so where is the delay coming from? The answer, as it turns out, is that IPv6 handling is getting in the way and slowing us down.

I should mention that I’m running this application on a Windows Server 2008 system, which uses IIS 7 as the web server. By default, in the Windows Server 2008 hosts file, you’re given two hostname entries:

127.0.0.1 localhost
::1 localhost

I found that if I commented out the IPV6 hostname (the second line), things sped up dramatically. PHP bug #45150, which has since been marked “bogus,” helped point me in the right direction to understanding the root cause. A comment in that bug pointed me to an article describing MySQL connection problems with PHP 5.3. The article dealt with the failure to connect, which happily wasn’t my problem, but it provided one useful nugget: namely that the MySQL driver is partially responsible for determining which protocol to use. Using this information in my search, I found a helpful comment in MySQL bug #6348:

The driver will now loop through all possible IP addresses for a given host, accepting the first one that works.

So, long story short, it seems as though the PHP MySQL driver searches for the appropriate protocol to use every time (it’s amazing that this doesn’t get cached). Apparently, Windows Server 2008 uses IPV6 routing by default, even though the IPV4 entry appears first in the hosts file. So, either the initial IPV6 lookup fails and it then tries the IPV4 entry, or the IPV6 route invokes additional overhead; in either case, we get an additional delay.

The easiest solution, therefore, is to continue using 127.0.0.1 as the connection address for the database server. Disabling IPV6, while a potential solution, isn’t very elegant and it doesn’t embrace our IPV6 future. Perhaps future MySQL drivers will correct this delay, and it might go away entirely once the world switches to IPV6 for good.

As an additional interesting note, the PHP documentation indicates that a local socket gets used when the MySQL server name is localhost, while the TCP/IP protocol gets used in all other cases. But this is only true in *NIX environments. In Windows, TCP/IP gets used regardless of your connection method (unless you have previously enabled named pipes, in which case it will use that instead).

Randomizing MySQL Query Results

Published on March 18, 2009

When I added the favorite photos feature to my photo album software, I wanted a way to randomly show a subset of said favorites on the albums display page. I initially thought about implementing my own means of doing this through PHP. Ultimately, I wanted random selection without replacement, so that viewers would not see multiple copies of the same image in the ‘Favorites Preview’ section. Thankfully, MySQL saved the day!

When sorting a MySQL query, you can opt to sort randomly:

SELECT {some columns} FROM {some tables}
WHERE {some condition} ORDER BY rand()

The rand() function in PHP essentially gives you random selection without replacement for free! How great is that? It was an easy solution to a not-so-simple problem, and saved me a lot of programming time.

Update: I have since learned that the ORDER BY rand() call is horribly inefficient for large data sets. As such, it should ideally be avoided. There’s a great article describing ways to work around these performance limitations.

Unicode and the Web: Part 1

Published on July 25, 2008

Dustin and his wife recently uncovered an interesting limitation of my Monkey Album software: characters outside of the ISO-8859-1 (Latin 1) character set don’t render properly. This comes as no surprise, seeing as I didn’t design for Unicode. Being a rather egregious display error, I decided to set out and fix the problem. In the process, I learned quite a lot about Unicode, and how it affects web applications. This post will be the first of two detailing how to add Unicode support to a web application. I will only be exposing a tip of the Unicode iceberg in these posts. The ideas and practices behind Unicode support can (and do) fill the pages of many books. That said, let’s jump in.

Read the rest of this entry »

Sun Purchases MySQL

Published on January 17, 2008

It seems as if Sun Microsystems has purchased MySQL. I don’t fully understand the motivation behind this purchase, but Sun must have some plan; otherwise they wouldn’t have paid one billion dollars for the company. In my opinion, Sun doesn’t have the greatest track record in software, so it should be interesting to see what happens as a result of this change. According to the official MySQL post:

Will MySQL’s support for other programming languages and operating systems now be given less attention? Absolutely not. MySQL is still being managed by the same people, and the charter is still the same.

We can only hope.

Understanding Prepared Statements

Published on January 15, 2007

While working on my rewrite of Monkey Album, I ran into an interesting programming dilemma. In the past week or so, I’ve been introduced to the MySQLi extension in PHP. The current Monkey Album implementation makes use of the PHP 4 mysql_*() calls, so I thought I’d try out the MySQLi interface to see how it works.

MySQLi includes support for what are known as “prepared statements” (only available in MySQL 4.1 and later). A prepared statement basically gives you three advantages: (1) SQL logic is separated from the data being supplied, (2) incoming data is sanitized for you which increases security, and (3) performance is increased, since a given statement only needs to be parsed a single time.

It seems to me that the performance benefit can only be seen in situations where the query is executed multiple times (in a loop, for example). In fact, an article on prepared statements confirms this suspicion; the author in fact mentions that prepared statements can be slower for queries executed only once.

So here’s the problem I face: the queries that get executed in Monkey Album are, for the most part, only ever executed once. So, do I make use of prepared statements just to get the security benefit? It doesn’t seem worth it to me, since I can get the same security by escaping all user input (something I already do today). Does someone with more knowledge of this stuff have an opinion? If so, please share it.

Counting Items in Multiple Tables With MySQL

Published on October 13, 2006

While working on my photo album software, I ran into an interesting SQL problem. I wanted to be able to display information about my photo albums, along with the number of images in each album. The problem is that my data is broken up into two tables: an albums table and an images table. My goal was to use exactly one SQL query to access all of the data, including the count of images. And I wanted empty albums (no images) to also show up in the query’s results. But try as I might, I couldn’t get the query to return the data I wanted. I finally found a solution that works, and I present an example below.

Let’s suppose we have two MySQL tables: one that represents directories, and another that represents files. The directories table has the following columns:

  • ID
  • Name

And the files table has the following columns:

  • ID
  • Parent_ID
  • Name

The Parent_ID field in the files table corresponds to the ID field in the directories table. In order to select both the count of files in each directory, as well as all of the directory information, we do a simple join. But here’s the trick: the order of your tables matters! Here’s the query that works for this scenario:

SELECT d.*, Count(f.ID) AS Count FROM directories d LEFT JOIN files f ON f.Parent_ID = d.ID GROUP BY d.ID

When the tables are reversed in the JOIN, only tables with 1 or more entries show up in the results. What a subtle change! Hopefully someone will find this tip useful. It sure took me a while to get this working.

Copyright © 2004-2018 Jonah Bishop. Hosted by DreamHost.