Top 10 MySQL Mistakes Made By PHP Developers

Learn more on MySQL with our screencast MySQL on the Command Line.

A database is a fundamental component for most web applications. If you’re using PHP, you’re probably using MySQL–an integral part of the LAMP stack. PHP is relatively easy and most new developers can write functional code within a few hours. However, building a solid, dependable database takes time and expertise. Here are ten of the worst MySQL mistakes I’ve made (some apply to any language/database)…

1. Using MyISAM rather than InnoDB

MySQL has a number of database engines, but you’re most likely to encounter MyISAM and InnoDB. MyISAM is used by default. However, unless you’re creating a very simple or experimental database, it’s almost certainly the wrong choice! MyISAM doesn’t support foreign key constraints or transactions, which are essential for data integrity. In addition, the whole table is locked whenever a record is inserted or updated; this causes a detrimental effect on performance as usage grows. The solution is simple: use InnoDB.

2. Using PHP’s mysql functions

PHP has provided MySQL library functions since day one (or near as makes no difference). Many applications rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc. but the PHP manual states:

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead.

mysqli, or the MySQL improved extension, has several advantages:

an (optional) object-oriented interface
prepared statements (which help prevent SQL-injection attacks and increase performance)
multiple statements and transaction support

Alternatively, you should consider PDO if you want to support multiple databases.

3. Not sanitizing user input

This should probably be #1: never trust user input. Validate every string using server-side PHP — don’t rely on JavaScript. The simplest SQL injection attacks depend on code such as:


$username = $_POST["name"];
$password = $_POST["password"];
$sql = "SELECT userid FROM usertable WHERE username='$username' AND password='$password';";
// run query...

This can be cracked by entering “admin'; --” in the username field. The SQL string will equate to:


SELECT userid FROM usertable WHERE username='admin';

The devious cracker can log in as “admin”; they need not know the password because it’s commented out of the SQL.

4. Not using UTF-8

Those of us in the US, UK, and Australia rarely consider languages other than English. We happily complete our masterpiece only to find it cannot be used elsewhere. UTF-8 solves many internationalization issues. Although it won’t be properly supported in PHP until version 6.0, there’s little to prevent you setting MySQL character sets to UTF-8.

5. Favoring PHP over SQL

When you’re new to MySQL, it’s tempting to solve problems in the language you know. That can lead to unnecessary and slower code. For example, rather than using MySQL’s native AVG() function, you use a PHP loop to calculate an average by summing all values in a record-set. Watch out also for SQL queries within PHP loops. Normally, it’s more effective to run a query then loop through the results. In general, utilize the strengths of your database when analyzing data. A little SQL knowledge goes a long way.

6. Not optimizing your queries

99% of PHP performance problems will be caused by the database, and a single bad SQL query can play havoc with your web application. MySQL’s EXPLAIN statement, the Query Profiler, and many other tools can help you find that rogue SELECT.

7. Using the wrong data types

MySQL offers a range of numeric, string, and time data types. If you’re storing a date, use a DATE or DATETIME field. Using an INTEGER or STRING can make SQL queries more complicated, if not impossible. It’s often tempting to invent your own data formats; for example, storing serialized PHP objects in string. Database management may be easier, but MySQL will become a dumb data store and it may lead to problems later.

8. Using * in SELECT queries

Never use * to return all columns in a table–it’s lazy. You should only extract the data you need. Even if you require every field, your tables will inevitably change.

9. Under- or over-indexing

As a general rule of thumb, indexes should be applied to any column named in the WHERE clause of a SELECT query. For example, assume we have a usertable with a numeric ID (the primary key) and an email address. During log on, MySQL must locate the correct ID by searching for an email. With an index, MySQL can use a fast search algorithm to locate the email almost instantly. Without an index, MySQL must check every record in sequence until the address is found. It’s tempting to add indexes to every column, however, they are regenerated during every table INSERT or UPDATE. That can hit performance; only add indexes when necessary.

10. Forgetting to back up

It may be rare, but databases fail. Hard disks can stop. Servers can explode. Web hosts can go bankrupt. Losing your MySQL data is catastrophic, so ensure you have automated backups or replication in place.

11. Bonus mistake: not considering other databases!

MySQL may be the most widely used database for PHP developers, but it’s not the only option. PostgreSQL and Firebird are its closest competitors; both are open source and not controlled by a corporation. Microsoft provide SQL Server Express and Oracle supply 10g Express; both are free versions of the bigger enterprise editions. Even SQLite may be a viable alternative for smaller or embedded applications. Have I missed your worst MySQL mistakes?

Learn more on MySQL with our screencast MySQL on the Command Line.

Frequently Asked Questions (FAQs) about MySQL Mistakes by PHP Developers

Why is it important to avoid using the SELECT * statement in MySQL?

The SELECT * statement is often used by PHP developers for its simplicity and convenience. However, it can lead to performance issues, especially when dealing with large databases. This statement retrieves all columns from a table, even those that are not needed, which can consume unnecessary resources and slow down your application. Instead, it’s recommended to specify the exact columns you need in your SELECT statement. This not only improves performance but also makes your code more readable and maintainable.

How can I prevent SQL Injection attacks in PHP?

SQL Injection is a common security vulnerability in web applications. It occurs when an attacker can insert malicious SQL statements into an SQL query. To prevent this, you should always use prepared statements or parameterized queries. These techniques ensure that user input is always treated as literal data, not part of the SQL command. PHP provides built-in functions for this, such as mysqli_prepare() and PDO::prepare().

Why should I avoid using MySQL extension functions in PHP?

The MySQL extension functions in PHP, such as mysql_connect() and mysql_query(), are deprecated as of PHP 5.5.0 and removed in PHP 7.0.0. They are not secure and lack an object-oriented interface. Instead, you should use MySQLi or PDO_MySQL extensions. These extensions provide more features, including prepared statements, multiple statements, transactions, and an object-oriented interface.

What is the significance of using UTF-8 encoding in MySQL?

UTF-8 is a universal character encoding standard that supports all characters and symbols from all human languages. By using UTF-8 encoding in MySQL, you ensure that your database can store and correctly display any kind of text, no matter what language it’s in. This is especially important for web applications that need to support multiple languages.

Why should I avoid using PHP’s mysql_real_escape_string() function?

The mysql_real_escape_string() function was used in the past to escape special characters in a string before sending it to MySQL. However, this function is not safe against SQL Injection attacks and is deprecated as of PHP 5.5.0. Instead, you should use prepared statements or parameterized queries, which are much safer and more efficient.

How can I optimize my MySQL queries for better performance?

There are several ways to optimize your MySQL queries for better performance. First, always specify the exact columns you need in your SELECT statements. Second, use JOINs instead of subqueries whenever possible. Third, use indexes on columns that are frequently searched or sorted. Fourth, use the EXPLAIN statement to analyze your queries and find potential bottlenecks.

What is the difference between MySQLi and PDO_MySQL in PHP?

Both MySQLi and PDO_MySQL are PHP extensions for MySQL database. MySQLi, which stands for MySQL Improved, is a procedural and object-oriented extension. It supports prepared statements, multiple statements, transactions, and more. PDO_MySQL, on the other hand, is a part of the PHP Data Objects (PDO) extension, which provides a consistent interface for accessing various databases, not just MySQL. It also supports prepared statements and transactions.

Why should I use a database abstraction layer in PHP?

A database abstraction layer provides a consistent API for database operations, regardless of the underlying database system. This means you can switch to a different database system without changing your PHP code. It also provides additional features, such as query builders, schema builders, and an object-relational mapping (ORM) system, which can greatly simplify your database operations.

How can I handle MySQL errors in PHP?

You should always check the return value of your MySQL functions and handle errors appropriately. PHP provides several functions for this, such as mysqli_error() and PDO::errorInfo(). You can use these functions to get detailed information about the error and display a user-friendly message or log the error for debugging.

Why should I avoid using persistent connections in MySQL?

Persistent connections in MySQL are connections that do not close when the execution of your script ends. While they can improve performance by eliminating the overhead of establishing a new connection for each request, they can also consume a lot of system resources, especially on a busy server. Therefore, you should use them sparingly and only when necessary.