Common errors when using MySQL

[Spy$Eyes]

The databases are an indispensable in web page development, developers often can be decanted from different programming languages (PHP, Python, Ruby on Rails, etc) but in the end almost all use MySQL as a principal and indispensable for managing data. Personally I think it's relatively easy to write functional code in a few hours and join a good design, which together form a useful application, but such combination tend to not scale well due to its high dependency on the database, so in the vast majority of times we would rather invest time in analyzing and optimizing the data structure you are using. These are some common mistakes that are often committed in MySql.

Use InnoDB instead of MyISAM

Are the two database engines most famous and the use of one or the other is a very important decision. MyISAM is used by default but not the best option if you are thinking of a pilot project is a good choice, but in most cases is not the best solution, we will mention some of the key ways that is further recommended the use of InnoDB.

1. MyISAM does not support foreign keys
2. Data Integrity
3. In each script MyISAM locks the whole table so that each "INSERT" or "UPDATE" to block the table, a small scale but is not usually noted as a requirement of the database is greater than the decrease performance is considerable.

[Spy$Eyes]

No input filter

Never trust user input in how data is concerned, to make sure this filter and clean the data on the server side and thus avoid unexpected errors and SQL injection attacks.

Do not use UTF-8

UTF-8 we solve many problems in terms of internationalization of applications is concerned.

Filtering Data with PHP rather than MySQL

When you are new to MySQL often try to solve problems when removing and filtering data with PHP this is a serious mistake because it is much slower, you might have to waste time researching but I'm sure this problem you have there a solution with MySQL. A simple example would take the average value in a table in the database, if you know of MySQL certainly try to get the total in PHP and divided the sum by the number of rows that you get to the table, this in MySQL can easily be done using function AVG (). Remember that in almost all cases will be more effective a good consultation with MySQL.

Do not optimize queries

As mentioned above the vast majority of errors when scaling a project are in the database, so it is very important to take a time query optimization, the choice of indices, the size of the fields and the filters are going to use are necessary questions when face to a query.

[Spy$Eyes]

Misuse of data type

There are a number data type, small-scale surely as a bad choice but as our database grows if the data type chosen is wrong the problems we have are going to be great. So before using a DATETIME think if in this particular case would be more advisable to use a DATE, or sign if you know a field's value will not exceed a certain amount of numbers look if you are interested in using a TINYINT instead of INTEGER .

Use * in SELECT queries

Rarely has to resort to using the * in the SELECT query, for various reasons, the first and main reason is that you're probably collecting more data than you need, another reason I really do not know exactly which fields are recovering from the database and the query have a JOIN the plot thickens considerably. It is best not to ever use this resource only in exceptional cases and try at all times exactly the data you need.

[afidelino]

I would add something like "Error 1:", "Error 2:" in the titles of each error, because the title seems to contradict what is intended to express. It can be confusing. For example: "Error 3: Do not use UTF-8." Query optimization for the final would add, as a good practice, using a PHP cache (memory or file as appropriate) over the database to prevent repetitive queries to the mysql server. Furthermore, in entire agreement on the information given.