webslave.ws

Everybody involved in the world of developing dynamic web applications knows about the excellence of a RDBMS as MySQL Server. It is one of the most used database management system with more than 11 million installations around the globe. MySQL is a database server which provide for developers and users all the features you require from database management system. It is important you know that MySQL has many unique features that you won’t find any database in the market such as several storage engines, for select the most effective for each table in your application, the storage engine is native from MySQL and much others.

One of the most used operations in MySQL is the query. Queries in MySQL are fast and reliable and you can optimize it in order you can get the most out of your data and your web application in general. I would like to share with you some important tips for optimize your queries in MySQL Server.

• Importance of use the explain command: You can use the INSERT statement store several rows with a unique SQL statement. This command tells you how to use the correct indexes on a specific query. Whether you have a database named (db_mysql_example) you can make a simple select typing this: [EXPLAIN SELECT * FROM db_mysql_example].

• Avoid complex permissions: When you are administering your databases try to set your permissions correctly, safety but never complex. The more difficult you set the database permissions the more overhead your will have. When users execute a statement the database engine will check the permission first. If you have simple permissions you will get a database with a better level of performance and executing faster transactions.

• You can optimize the “where” clauses: Each character you type is very important for the performance in MySQL. I think it’s the difference between good database programmers and excellent database programmers. You can reduce unnecessary parentheses within the clauses. For instance you can make a COUNT(*) on a single table without needing to use WHERE. Also using the option SQL_SMALL_RESULT, MySQL will use a temporary table located in memory.

• Use the “Insert Delayed”: If you want to get a better performance and improve the operability of your database in MySQL you must have to use insert delayed. Using it you’ll reduce the insertion impact because various rows can be written with only one disk write.

• Use the “optimize table”: In the same way you can use third-party software for defragmenting your hard disc, using optimize table you’ll be able to defragment a table after you delete several rows from it.

• Using Statement priorities: If you need establish that SELECT statement get a higher priority than your insert you can use “INSERT LOW PRIORITY”. Also if you want to get retrievals that go beyond the queue, you must have to use SELECT HIGH_PRIORITY and the SELECT will be executed even if you have a queue of users waiting.