MySQL is the most popular open-source database system. The data in MySQL is stored in database objects called tables.A table is a collection of related data entries and it consists of columns and rows. The database is the basis for most Web application development. If you are using PHP, then the important part of most of the database with MYSQL is also the LAMP architecture. PHP looks as it is very simple, a beginner can also start to write functions within a few hours. But to establishment a stable, reliable database does take time and experience. Here is some of the experience, not only for MYSQL, but for other databases.
1. Use MyISAM Instead of InnoDB
MySQL database engine, but in general will use the MyISAM and InnoDB. MyISAM is the default used. But unless you establish a very simple database or just experimental, then the choice of MyISAM is wrong in most time. MyISAM does not support key constraints, which is the essence to ensure data when integrity. In addition, the entire table will be locked while MyISAM add or update data, this is a big problem in the subsequent expansion of performance.
The solution is simple: use the InnoDB.
2. Use MySQL Technique
MySQL library was first provided in PHP. Many programs rely on the mysql_connect, mysql_query, mysql_fetch_assoc and so on, but recommended in the PHP manual:
If you are using MySQL which is up to version 4.1.3 it is strongly recommended to use the mysqli extension. Mysqli, or that the MySQL extension, there are some advantages:
1).Object-oriented interface
2). Prepared statements (prepared statement, you can effectively prevent SQL-injection attacks, but also improve performance)
3). Support a variety of statements and transaction
In addition, if you want to support multi-database then you should consider using the PDO.
3. Do Not Filter User Input
Never trust user input. Filter back-end PHP to verify information entered in each one, do not trust Javascript. Otherwise, it is very easy to be attacked like the following SQL statement:
$ username = $ _POST [“name”]; $ password = $ _POST [“password”]; $ sql = “SELECT userid FROM usertable WHERE username = ‘$ username’AND password =’ ??$ password ‘;”; / / runthe query…
This code if the user input”admin’;This, then is equivalent to the following:
SELECT the userid FROM usertable the WHERE username= ‘admin’;
Then the intruder doesn’t need to enter a password by logged in as admin.
4. Do Not Use UTF-8
Users of those Anglo-Saxon countries, with little regard for the language, would have resulted in many products can not be generic in other places.. There are some GBK encoding, there will be a lot of trouble.
UTF-8 can solve a lot of the issue of internationalization. Although PHP6 can be the perfect solution to this problem, but does not prevent the MySQL character set to UTF-8.
5. Using PHP Instead of SQL
If you are new to MySQL, sometimes you may consider using the language you are familiar with to solve problems. This may result in waste and relatively poor performance. Also, pay attention to the cycle of SQL queries in PHP. Typically, it is more efficient to cycle by using PHP after obtained all the results. Generally, when dealing with large amounts of data using a powerful database can increase efficiency.
6. Does Not Optimize The Query
99% of the PHP performance issues are caused by database. A bad SQL statement that may make the entire process slow down. MySQL’s EXPLAIN statement, Query Profiler, many other tools, these tools can help you find those naughty SELECT.
7. Using The Wrong Data Type
MySQL provides a series of numbers, strings, and time data types. If you want to store the date, then, is to use the DATE or DATETIME type, the use of plastic or string make things more complicated.
Sometimes you want to use their own defined data types, for example, using the string stored serialized PHP objects. Add the database may very easily, but this is the case, MySQL will become very heavy, and that may lead to some problems.
8. * Used In The SELECT Query
Do not use the * to return all the fields in the table, it will be very slow. You only need to remove the data fields you need. If you need to remove all the fields, then your table may need to be changed.
9. Insufficient or Excessive Index
In general, the index should appear in the SELECT statement in all the fields that behind WHERE. For example, if the user table with a digital ID (primary key) and email address. After logging in, MySQL via email to find the corresponding ID. Through index, MySQL can quickly locate e-mail via search algorithm. If there is no index, MySQL will need to examine each record until you find.
In this case, you may want to add an index to each field, but the consequences of doing so is when you update or add, the index will re-do it again, there will be performance issues when there to be too large data. Therefore, the index only in the required fields.
10. Did Not Do The Backup
May occur infrequently, but the database is corrupted, bad hard drive, the service is stopped, these will cause catastrophic damage to the data. So you must ensure that the operation will automatically back up your data or save a copy.
Though not the only option, MySQL may be the most used in PHP database.
Published by & last updated on May 28, 2013 10:15 am
Leave a Reply
You must be logged in to post a comment.