Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Common errors when using MySQL

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 05-01-2011
Member
 
Join Date: Jul 2010
Posts: 142
Common errors when using MySQL
  

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.

Reply With Quote
  #2  
Old 05-01-2011
Member
 
Join Date: Jul 2010
Posts: 142
Re: Common errors when using MySQL

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.
Reply With Quote
  #3  
Old 05-01-2011
Member
 
Join Date: Jul 2010
Posts: 142
Re: Common errors when using MySQL

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.
Reply With Quote
  #4  
Old 05-01-2011
Member
 
Join Date: Apr 2008
Posts: 242
Re: Common errors when using MySQL

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Common errors when using MySQL"
Thread Thread Starter Forum Replies Last Post
How to fix common Outlook 2007 and Exchange Sync errors SirName Windows Software 8 25-06-2012 10:27 AM
Unable to install MySQL and MySQL Workbench on Linux mint 10 Zared Windows Software 4 13-01-2011 12:08 AM
mysql config file in MySQL Netorious Software Development 4 18-03-2010 09:43 PM
Mysql Error : Can't connect to local mysql server through socket ' var lib mysql mysql.sock' 2 roshan45 Software Development 3 07-11-2009 08:36 PM
Maharashtra Common Entrance (MHT-CET) Rahul45 Education Career and Job Discussions 0 09-02-2009 09:33 AM


All times are GMT +5.5. The time now is 09:55 PM.