Ever seen a DBA having to deal with an application developers naive mistakes? Or could it simply be a case of developers just not knowing what should be done on a database and why it should be done. This issues arise straight from poor uses of SQL statements to sheer negligence in the total design of databases.
So here is a look through of common mistakes made by application developers when they dive into database development.
1. Not using prepared statements
Is where the query is pre-compiled by the database rather than the database having to do it every time it encounters a new query. This means that when a database management system encounters a query that has been previously used, the database can cache the compiled query and set u an execution plan. Prepared statements can also protect you against SQL injection attacks. A prepared statement would look something like this:
Replace this Statement
SELECT name FROM employees WHERE department = ‘finance’
with either of these… notice the difference?
SELECT name FROM employees WHERE department = ?
SELECT name FROM employees WHERE department = : department
2. Not Using Indexes
This is does happen a lot. To have an idea on what should be indexed simply lies in the fields that you intend to use in the WHERE clause and possibly extend you indexes on multiple columns depending on the intended queries. Foreign Keys also need to be indexed.
3. Not Sanitizing The Input into the Database
Never should one at anytime insert or update data in a database from direct user input from URLs, form data and cookies as this leaves sites and applications vulnerable to malicious attacks. The general rule of thumb is to make sure that all data should be treated as hostile and therefore must be sanitized. When using different languages and databases for your application the methods of sanitizing these application vary. Here is an example of using PHP and MySQL.
Bad
mysql_query(“SELECT department FROM employees WHERE name = ‘{$_POST['username']}’”);
Good
mysql_query(“SELECT department FROM employees WHERE name = ‘”.mysql_real_escape_string($_POST['username']).”‘”);
4. Not Using Constraints
Even when using constraints they must be used within the database and not third party implementations such as using PHP to enforce constraints in MySQL. This is true for simple reason that you can never know what you will want to change in your database. Change is Inevitable. When that change comes you want to be able to make it in a central location; the database.
It is also important to have constraints in the database for times when for example, several developers are working on an application and one of them may really not understand the integrity requirements of the data and within their code they make an error that could possibly corrupt the data. Constrains would come in handy to trap those errors.
5. Not Using Joins in Favor of Aggregation (Ex. GROUP BY clause)
A lot of developers may not know this but using aggregation such as GROUP BY may be twenty times or more slower than using joins wit indexes. The execution time when using aggregation in queries tends to be directly proportional to the size of the table as every row processed, aggregated and filtered.
Apparently in most cases this is not the same with joins as they tend to select a subset of the data based on a given role then check that subset based on a second role and so on. So these two queries from here pretty much give the same results but with a huge difference in execution time.
First query:
SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3Query time: 0.312s
Second Query:
SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1Query time: 0.016s
6. Not Properly Normalizing The Database Tables
This is a problem that rides on the notion that too much or too little of everything is bad for you. Normalizing the tables too much is not a good thing neither not normalizing the database tables is also a mess. Getting the balance right is the tricky part but reaps great rewards in terms of database performance as well as proving the integrity of the data.
So what is database normalization? It simply is basically how you optimize the database design and organizing you data into tables. If you have to join say ten database tables to extract meaningful data then you have probably encountered database tables that have been normalized too much. On the other hand, if you encounter a database schema where say there is a list of zip codes appearing in 7 of the tables then that could be a hint that that database needs to be normalized.
7. Not Doing Performance Tuning and Analysis
This goes to lean towards the idea that testing is needed on your queries and not just that they work. There are several occasions I have encountered where a database crawls when a table is choked with a few hundred thousand rows, and the same table performs like super man when it only has a few thousand rows. This is very common when the application developer doesn’t bother to do performance analysis simply because they didn’t anticipate that when it comes to databases, numbers count.
I recommend loading random data into database tables during development say to the tune of 500,000 to 1,000,000 records and see what happens when executing the data.
Note: you would need to develop a tool to do this loading of random data for you.
8 Not Using IN instead of OR
When using several OR conditions and the different OR conditions intersect it forces the optimizer to do a DISTINCT operation on the result.
One such example is
... WHERE id = 1 OR id = 2 id a = 3
Here is a better way:
... WHERE id IN (1, 2, 3)
There you have it, eight common mistakes application developers tend to make while doing database development tasks.
To learn more about Sobbayi Interactive’s services or to find out how to get your very own custom applications for your business developed by our super-talented team, email us today at info@sobbayi.com or get started here.
