Wednesday, December 1, 2010

MySQL Mistakes by PHP Developers

Database is a vital part of most needed for almost all applications there are. If PHP is what you are using, it is most likely MySOL that you are using. MySQL is an essential part of LAMP stack.

For a lot of developers, PHP is quite easy to write. Functional codes, to developers, can be written in a few hours. But to be able to build a concrete and reliable database can surely take a lot time and excellent skills.

It is important to take note of the most common MySQL mistakes made my PHP developers.

Ø The usage of MyISAM instead of InnoDB

MySQL hold a number of engines f database, but it is very common for anyone to encounter MyISAM or InnoDB.

MyISAM is commonly used not by choice but by default. But if you are not creating a simple or investigational database, it is wrong to use MyISAM, simply because it does not support constraints and transactions of foreign keys, which cannot be taken for granted because they are ne f the fundamentals of data integrity. Added to this, each time a record is updated and inserted, the whole table is protected with a lock and of its effects takes on the performance during further usage.

Ø PHP MySQL functions usage

PHP has been a provider of functions for MySQL from the beginning. A lot of applications depend on mysql_query, mysql_connect, mysql_fetch_assoc and many more. But it is written on the PHP manual that if you use version 4.1.3 of MySQL or anything later than that, it is highly recommended to use extension of mysqli as a substitute.

Improved MySQL extension or the mysqli has a lot of advantages: You get an optional interface that is object-oriented, various statements & support for transaction, and statements prepared that can help prevent attacks of SQL-injection and at the same time, boost performance.

If you would want to support various databases, you should consider using PDO as an alternative.

Ø Choosing not to sanitize User Input

Ø Choosing not to use UTF – 8

Most developers from Australia, Uk and US very seldom consider the usage of non-English language. In effect, we go on with our masterpiece and finish and alas, we cannot use it in any other places. That is why using UTF – 8 can help in solving a lot of problems that have something to do with internationalization. Its usage can be hard for non-PHP version 6.0 but the number of things to prevent is lowered when you set your MySQL character to UTF – 8

Ø Ditching SQL over PHP

It is very much enticing to solve equations in the language you are familiar with especially if you have just started using MySQL. But that can very much bring you to unwanted and a lot slower code. For instance, instead of choosing to use MySQL’s local AVG () function, you resort to using a loop of PHP to compute an average through adding up all record-set values.

It would be good to watch out for queries of SQL within loops of PHP. It is normally much more effective to make a query run and then use loop for the results.

So generally, utilizing the strong points of the database for data analyzing is good. A little know-how on SQL goes a longer way than you think.

Ø Choosing not to optimize the queries

Big percentage of the problems in PHP performance revolves is a cause of the database and just one not-so-good SQL query can cause problems to your process of web application. You can look up that rogue select in either MySQL’s EXPLAIN statement or Query Profiler.

Ø Incorrect data types usage

MySQL offers an array of time data types, numeric and string. If it is date you are storing, use DATETIME of DATE field. Using either string or integer can complicate SQL queries or worse, painfully impossible.

It is enticing to make an inventin of your very own formats of data. For instance, storing PHP objects that are serialized. Database management has a chance to be easier, but MySQL will turn into a senseless data store and may lead to possible problems.

Ø Usage of “*”for SELECT queries

DO NOT ever use * to put back all of the columns to a table. That is certainly a lazy way to do it. You should only extract the data needed. Your tables are susceptible to inevitable change even if you have ever field required.

Ø Wrong indexing

In general rule, indexes are applicable to any column and should be applied. This talks about columns named under the SELECT query WHERE clause.

For instance, let’s say we have a usertable that has a numeric ID and one email address. The correct ID must be located by MySQL during log on, through email search. If you have an index, fast type of search algorithm can be used by MySQL. This is to instantly locate the email. But if you do not have an index, every record in sequence must be checked by MySQL until the address is located.

Ø Not reminding oneself to back up

Though failing databases may be rare, it can still happen. Hard Disks can be put on halt and servers can crash. Servers can also all bankrupt. To lose MySQL data can be painfully disastrous. So do make sure that you have automated backing up or a replica in place.

Ø Not being able to put other databases in to account!

It is possible that MySQL is the mostly what PHP developers use. But it is never the only choice. FireBird and PostgreSQL stand close as MySQL’s competitors. Both of which are open source and uncontrolled by any sort of corporation. SQL server expresss is provided by Microsoft. 10g Express is provided by Oracle. These two are versions from editions of bigger enterprise and they are FREE, and not to mention SQLite which may be a feasible replacement for applications that are smaller or embedded.