6 Common Mistakes you should Avoid while Using MySQL

Every single developer, regardless of actual industry experience, is familiar with the fundamental component of web application development, i.e., database. Almost all back-end developers coding in PHP using LAMP or XAMPP stack are working with MySQL. This article is about to highlights the Common Mistakes Developers Avoid while Using MySQL.

It’s easy to get a grasp of PHP, even the novice developers can get started on writing PHP and come up with simple, functional scripts within a few hours. Building a sturdy, secure, and consistently reliable database in MySQL through PHP, however, will require more time and experienced hand.

List of Common Mistakes Developers Avoid while Using MySQL

To somewhat expedite that experience gain, you can read about others’ mistakes and learn from them.With that goal in mind, here are 6 of the most common MySQL mistakes PHP developers make:

1.Wrong database engine

Almost any number of similar articles out there will tell you this: Just go with InnoDB. And considering the level of active development, it makes sense. It’s a safe bet. But I would say research your options before you choose a database engine, because yes, you have more than MyISAM and InnoDB out there.

Consider the following:

• Data Usage: How will you use the data? Is there a pattern? Is it just an archive, an in-memory database for look ups, or both? What is the use pattern, is it just an archive or an in memory look up, or something in between?
• Compatibility and Migration: MySQL is owned by Oracle. Is your engine compatible with MariaDB or similar options should you need to migrate?
• Other Options: XtraDB, PBXT, etc…
• Will you need to use SPhinx or Lucene?

InnoDB is the default choice for over 90% of people and serves well for most needs. The row level locking and ease of transactions make it so much friendlier to work with. But still, go with the engine that serves your requirements. Don’t jump on the bandwagon.

2. User-input and SQL injections

This is very important point to avoid the Common Mistakes Developers Avoid while Using MySQL.

First off: Stop using JavaScript to validate user input.
And secondly: Validate everything using server-side PHP.

A simple SQL injection can compromise your hard work and faith in humanity.

Escape user input to prevent that sort of malicious activity messing up your database. Use MySQLi’s real_escape_string function.

3. Not optimizing the entire Database (Focusing on just the queries)

With a whole web full of developers screaming ‘Performance optimization’, there’s really no one to be blamed but yourself when your MySQL database is riddled with bottlenecks that are impossible to locate and/or eliminate.
Instead of just optimizing queries through Query profiler, you should focus on optimizing your database for high performance. This is important to optimize the database which is part of Common Mistakes Developers Avoid while Using MySQL.

• Update: Always stay updated to the latest version of MySQL server software. Every new version usually comes packing huge performance improvements, if nothing else.
• Performance tuning scripts: These are the oldies but goodies of the lot. MySQL Tuning Primer Script, phpMyAdmin Advisor, etc. will give you recommendations to tune those server variables.
• Monitoring: Logging slow queries and mysqldumpslow aside, PHP users can use phpMyAdmin Monitor to find and fix slow queries.

4. When to Favor PHP over SQL

Again, this depends on the context.

When you are splitting the logic between SQL and PHP, what happens when you move away from SQL?
For example, in the course of writing this article, I saw a hundred copies that all said the same thing: Use MySQL’s AVG() function instead of a PHP loop.

Surprise: There’s absolutely no difference between the two, provided you are working with good data structures.
When your PHP scripts are handling more of the logic and grunt work, you are left with more freedom to point more web servers to the same well-made and fine-tuned database.

Basically, try to utilize the strengths of your database while creating the database. Your SQL knowledge should be used to make MySQL cleaner and more effective, not showing off how much you can get done without PHP.

5. Indexing too much (or not enough)

Database indexes help accelerate the speed of SELECT query statements. Although not very helpful for small tables, indexes can have a significant positive impact on performance when querying large tables with huge data.

Indexes are usually applied to a column named in the WHERE clause of the SELECT query. MySQL can locate data with a fast search algorithm with indexes.

And while it may be tempting to add indexes to every column, remember that they are regenerated after every INSERT and UPDATE query. If you are continuously adding more data to the table, use indexes only in columns you most use for SELECT queries.

“How much” indexing is subjective to the data usage and app. You need to review the usage pattern, slow query logs, etc. and keep a steadfast balance for best results. This way you can get rid of the common Mistakes Developers Avoid while Using MySQL

6. Not using UTF-8

Unicode is the great equalizer (read: encoder) of languages.

UTF-8 is a variable-width encoding that is capable of representing every character in the Unicode character set. UTF-8 is the dominant character encoding set for the internet; more than half the web pages use this system.

While it is comfortable to work with English-only character sets in MySQL, if you are working with the web, in general, you will most likely need to create databases for internationalization.

You can make modifications in your php.ini file, specify UTF-8 as the charset in all headers output by PHP codeIN, and set UTF-8 as encoding type for XML, HTML forms, content, special characters, and yes, as the default character set for MySQL connections using mysqli::set_charset.

You’ll also have to modify my.ini file or migrate an existing latin1 encoded database to UTF-8 because Latin1 with its less than 256 characters simply won’t cut it in the global age.

Endnote

It should be a no-brainer, but if you forget to backup your database, all your hard work can go down the drain before you could blink an eye in the events of hacking, server issues, database failure, etc. Data is your most valuable asset, so for goodness’ sake back it up regularly.