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' ";
$sql .= " 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? Write on the comments
