I was scouring the web for some MySQL tips, tricks, and documentation to help me as I work on the blog and other development projects. Tuning is often times overlooked; sometimes once the database table structure is built and normalized, development stops there. I am trying to be more conscious of that and during my research I came across a performance tuning video published by Google during one of their Google TechTalks. You can check it out at Google Video or view it embedded below.
If you have been using MySQL for your database choice, you'll probably find this cheatsheet to be pretty useful. Whether you are new to MySQL or advanced, this is pretty handy to have around. A help page on help.scibit.com offers a great breakdown of the different datatypes that are available for use in MySQL. They cheatsheet lists the data type, the reasons for use, and the size of the data type. This is a quick and easy reference that will hopefully help out.
MySQL Field Types [via dzone]
For those of you that are as hooked on phpMyAdmin as I am, you will be happy to know that there is a new version available. phpMyAdmin 3.1.0 includes a number of improvements and fixes.
The list of improvements includes security updates, language updates, export fixes, and general patches. The release notes highlight a few specific improvements: "this version has a new setup mechanism and supports BLOBstreaming and the Swekey hardware authentication" (release notes).
You can view the release notes for full details concerning the new release and download the new version from sourceforge.
Have you ever wondered if your skills are in demand? With so many different possibilities available for web development, it is tough to know if you should keep mastering a specific technology or start learning a new one. After reading an article about how PHP programming jobs are in high demand, I asked myself the follow-up question: what other skills do I have that are in high demand, and which skills do I have that are not?
The article linked to a great resource from oDesk that lists the current (monthly) trends for many of the development and design skills used for web development. There are 84 different trends with a wealth of useful information about the skill's demand including graphs, stats, and recent job postings. Although these stats are pulled from information specifically on oDesk, it represents demand from all around the world. I have listed links to some of the most in-demand skills right now.
As a side note, this site is a fantastic example of how great the Google Chart API is.
Techotopia offers free online books that cover a number of subjects including programming languages, security, and Linux. The books are hosted on their wiki and are available to read online. The four main books that brought me to this site are:
As the website states that the "IT Essentials series of books are designed to provide detailed information that is accessible to both experienced and novice readers" (Techotopia). The books are clear and concise, providing detailed explanations and examples to help you understand the content. Each book is made up of multiple chapters in a logical and easy to follow order. You can access the table of contents from every page which makes it easy to skip around quickly and easily to look at what you find interesting and useful.
As well as the four books mentioned above, you can find books on these topics:
As a developer I have often wanted to be able to test query speeds among other things. The problem is I didn't always have a usable data set sitting around that could meet the requirements of my test cases. To make a long story short, I wish I had GenerateData.com a long time ago. This is a great tool and has a lot of potential. It can save a lot of time when you are trying to create your own test data set to work with.
GenerateData.com is currently in version 2.1 (released July 25, 2008) and offers a nice set of features. You can generate data in many different data types like addresses, numbers, lorem ipsum text, email addresses, phone numbers, and more. Once you have chosen what kind of data you would like to generate, you can choose from five different export options. You are able to export the data in XML, Excel, HTML, CSV and SQL formats. The SQL export options gives you the choice of MySQL or Oracle syntax. You can generate up to 200 records at a time, unless you donate $20 or more, which allows you to generate up to 5000 records at a time.
The generator is written using PHP, MySQL, and JavaScript. You can use the online version or download a copy of the script for use on your own server. The downloadable version is licensed under GNU. The website has requirements and installation instructions to follow if needed.
A traditional web server is made up of many different components, including an http server, mail server, FTP server, and more. In days gone by, a webmaster would install each software package individually to get a custom web server up and running. These kinds of server setups offer many advantages, including testing, learning, and being able to have total control over the web server environment. Today there are many different options available that bundle each component into an easy single installation package.
The installation packages are basically split into two main categories, WAMP (Windows Apache MySQL PHP) servers and LAMP (Linux Apache MySQL PHP) servers. As you can see, they are installation packages that include the Apache httpd server, the MySQL database server, and PHP installed on either a Windows machine or a Linux/Unix machine. There are many different WAMP/LAMP installation packages available that contain the 4 major components, as well as other software to make a more full featured web server. These packages can vary slightly, while meeting the general requirements for running a web server. I have listed some of the most common packages available below.
WAMP
- Apache2Triad - This package includes an incredible list of features. It installs Apache with mod_python, mod_ssl, mod_perl, OpenSSL, and AWStats, MySQL with phpMyAdmin, PostgreSQL with pgAdmin and phpPgAdmin, xmail with PHPXMail and UebiMiau, SlimFTPD with PHPsFTPd, PHP with Pear and Smarty, Perl with CPAN, and Python with Spyce and wxPython.
- XAMPP - This package installs Apache with OpenSSL, MySQL with phpMyAdmin, PHP 4 and PHP 5, FileZilla FTP server, and Mercury Mail Transport System. You can also get Perl and Tomcat as an add-on.
- WampServer 2.0 - This is the new version of the WAMP5 server. It installs Apache, MySQL with PHPMyAdmin and SQLiteManager, and PHP. It doesn't have all the bells and whistles of the first servers listed, but it is reliable and has the ability to install many different releases of Apache, MySQL, and PHP.
- The Uniform Server - This package installs Apache, MySQL with phpMyAdmin, and PHP. It also includes SSL capabilities, but I couldn't find what software is used.
- SpikeWAMP - This package installs Apache, MySQL, and PHP. This version also includes the ability to install popular applications like Drupal, Joomla, Moodle, phpBB, and more. There is also an update channel that allows you to download and install the new beta versions of the applications. This is a nice and easy WAMP installation package.
LAMP
- XAMPP - This package installs Apache with OpenSSL, MySQL with phpMyAdmin, PHP 4 and PHP 5, FileZilla FTP server, and Mercury Mail Transport System.
- Apache Toolbox - This package installs one of the largest bundles of applications and mods I have seen; it boasts almost 100 different modules. Some of these include Apache, MySQL, Apache, WebDAV, OpenLDAP, and gd libraries. Take a look at the website to get the full listing.
- BitRock - This package is based on the BitNami stacks and installs Apache, MySQL with phpMyAdmin, and PHP. There are also some popular applications available for easy installation with BitRock, like phpBB, WordPress, Joomla, and Subversion just to name a few.
What LAMP/WAMP server do you use? Or do you set up all the pieces separately? Let us know in the comments.
.
phpMyAdmin 2.11.8.1 was released yesterday. The main reason for the updated release is to patch bugs and security issues. There were some XSS problems reported by Aung Khant from the YGN Ethical Hacker Group. The two main fixes are both XSS related, one having to do with frames and the other has to do with a lack of HTML escaping. You can read more about these by visiting the site which contains the news release about these bug/security fixes from phpMyAdmin. These vulnerabilities were considered very serious and have now been patched with this new release. Be sure to upgrade your copy to 2.11.8.1. You can download the new release from SourceForge.
Speed and efficiency are key factors when running a website. All code needs to be optimized, whether it is PHP, ASP, Python, or SQL. Internet users are notorious for having a low attention span, and a fast page load will help to keep their attention focused on what is on the page, rather that what is slowly being displayed on the page. This sparked some thought for me while I was at work the other day.
I work primarily out of an Oracle database and often have a list of values that a field needs to be checked against. In my opinion, it is nicer to look at the values in a list because it is cleaner and easier to follow. I wondered if the cleaner look of the "IN" statement was worth any performance difference between the "IN" and the "OR". Both statements provide the same output when used correctly so I was curious if one was faster than the other. I then tested the difference, and the "IN" statement came up short.
This test was conducted using MySQL (Version 4.1.22) because all of my websites run on MySQL and, dare I say, most other websites do as well.
I used a test database I had readily available (WordPress) to test 2 SQL statements, 1 using "IN" and 1 using "OR." Here are my results:
Results
The "IN" Statement
SELECT *
FROM wp_posts
WHERE post_author IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9 )
- Records selected: 60
- Execution Time: 0.0066 sec.
- Average Time Per Row: 0.00011
The "OR" Statement
SELECT *
FROM wp_posts
WHERE post_author = 1 OR post_author = 2 OR post_author = 3 OR post_author = 4 OR post_author = 5 OR post_author = 6 OR post_author = 7 OR post_author = 8 OR post_author = 9
- Records selected: 60
- Execution Time: 0.0064 sec.
- Average Time Per Row: 0.000107
Projections
Based on the average execution time per row, I have created a graph to show a scaling of the difference between execution times as the selected row count becomes larger. The results are as follows:
Discussion
I have tested this on a small scale with a simple test database, but these results could be substantial on a larger scale. With major websites like Yahoo! storing 2 petabytes of data and Ebay processing 10 billion records per day, you can see how quickly .0002 seconds can become a problem. Major websites will definitely have a better and faster setup running than I do. They will also have incredibly optimized queries, but we (the smaller scale) should work hard and do our best to make our SQL easily scalable. A query that works on a table with 1,000 records may not work on a table with 1,000,000 records.
How do you optimize your SQL and database setup? Give your tips and tricks in the comments.
I have used many shared hosting providers throughout my years as a webmaster. Shared hosts have many great points, namely, price, and then they have their major drawbacks. For me, one of the major drawbacks is the limited use of the machine on which your site is hosted.
For instance, I love phpMyAdmin for maintaining and managing my databases, but I am not a fan of the need to log in to backup the databases. The easiest way around that of course would be to execute the mysqldump command that comes bundled with MySQL. Unfortunately, most shared hosts, all that I have dealt with, disable the ability to use exec() or system() with PHP to execute the MySQL native command. If these were available, creating a cron job on a simple PHP file that ran mysqldump would have worked perfectly. However, this wasn't the case. After looking around for a long time at a possible way around this, I came up empty handed. Maybe I didn't look hard enough, or looked in all the wrong places, but I was left with nothing. I needed to back up my databases at regular intervals and found it frustrating to log into phpMyAdmin to do this each time. So I did what I should have done to begin with... wrote my own back-up tool.
It is pretty simple and self-explanatory. I did not create it as a function to be called, although you are more than welcome to do so. The main advantage to calling it, I think, would be to pass variables. For my needs, I simply set it up in the directory. I wanted it to run and back up whichever database needed to be. The script simply backs up the database specified in an sql file located in the same directory as the script itself. The file is named in this format, "DB_Backup_YourDatabase_YYYY-MM-DD.sql"; this could be easily modified to save anywhere you have permissions to save. The code is completely free and you can do what you would like with it. If you are feeling generous, throw a shout out my way or maybe even donate, but neither is not required.
This script was written and used on a server running PHP Version 4.4.2 and MySQL Version 4.1.22.
Most Commented