Follow these below points while writing a Query in Database
1. Optimize Your Queries for the Query Cache
Most MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast.
The main problem is, it is so easy and hidden from the programmer, and most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task.
2. Get Suggestions with PROCEDURE ANALYSE()
PROCEDURE ANALYSE() function will analyze the columns structures and the actual data in your table to come up with certain suggestions for you. It is only useful if there is actual data in your tables because that plays a big role in the decision making.
For example, if you created an INT field for your primary key, however do not have too many rows; it might suggest you to use a MEDIUMINT instead. Or if you are using a VARCHAR field, you might get a suggestion to convert it to ENUM, if there are only few unique values.
Example:select `Id` from users procedure_analyse()
3. Use “NOT NULL” If You Can
Unless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL.
First of all, ask yourself if there is any difference between having an empty string value vs. a NULL value (for INT fields: 0 vs. NULL). If there is no reason to have both, you do not need a NULL field.
NULL columns require additional space and they can add complexity to your comparison statements. Just avoid them when you can. However, I understand some people might have very specific reasons to have NULL values, which is not always a bad thing.
From MySQL docs:
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
4. Use ENUM over VARCHAR
ENUM type columns are very fast and compact. Internally they are stored like TINYINT, yet they can contain and display string values. This makes them a perfect candidate for certain fields.
If you have a field, which will contain only a few different kinds of values, use ENUM instead of VARCHAR. For example, it could be a column named “status”, and only contain values such as “active”, “inactive”, “pending”, “expired” etc..
There is even a way to get a “suggestion” from MySQL itself on how to restructure your table. When you do have a VARCHAR field, it can actually suggest you to change that column type to ENUM instead. This done using the PROCEDURE ANALYSE() call.
5. Unbuffered Queries
Normally when you perform a query from a script, it will wait for the execution of that query to finish before it can continue. You can change that by using unbuffered queries.
There is a great explanation in the PHP docs for the mysql_unbuffered_query()function:
“mysql_unbuffered_query() sends the SQL query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”
However, it comes with certain limitations. You have to either read all the rows or call mysql_free_result() before you can perform another query. Also you are not allowed to use mysql_num_rows() or mysql_data_seek() on the result set.
6. Store IP Addresses as UNSIGNED INT
Many programmers will create a VARCHAR(15) field without realizing they can actually store IP addresses as integer values. With an INT you go down to only 4 bytes of space, and have a fixed size field instead.
You have to make sure your column is an UNSIGNED INT, because IP Addresses use the whole range of a 32 bit unsigned integer.
In your queries you can use the INET_ATON() to convert and IP to an integer, and INET_NTOA() for vice versa. There are also similar functions in PHP calledip2long() and long2ip().
Example:
7. Almost Always Have an id Field
Every table have an id column that is the PRIMARY KEY, AUTO_INCREMENT and one of the flavors of INT. Also preferably UNSIGNED, since the value can not be negative.
Even if you have a users table that has a unique username field, do not make that your primary key. VARCHAR fields as primary keys are slower. And you will have a better structure in your code by referring to all users with their id’s internally.
8. LIMIT 1 When Getting a Unique Row
Sometimes when you are querying your tables, you already know you are looking for just one row. You might be fetching a unique record, or you might be just checking the existence of any number of records that satisfy your WHERE clause.
In such cases, adding LIMIT 1 to your query can increase performance. This way the database engine will stop scanning for records after it finds just 1, instead of going thru the whole table or index.
Example: do I have any users from Alabama?
9. Choose the Right Storage Engine
MySql allows you to use different engine types to store your data, each with its own advantages and drawbacks. The two main engines are MyISAM and InnoDB, and the differences between them affect much more than just performance.
| InnoDB | MyISAM |
|---|---|
| InnoDB supports Row-level Locking | MYISAM supports Table-level Locking |
| InnoDB designed for maximum performance when processing high volume of data | MyISAM designed for need of speed |
| InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS | MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS |
| InnoDB supports transaction. You can commit and rollback with InnoDB | MYISAM not supports transaction. You cannot commit and rollback with MYISAM. |
| InnoDB stores its tables and indexes in a tablespace | MyISAM stores its tables, data and indexes in disk space using separate three different files. (tablename.FRM, tablename.MYD,tablename.MYI) |
There is a list of what to do and what don’ts
| Do’s | Don’ts |
|---|---|
| Always write the required column names after the SELECT statement, like: SELECT StudentID, StudentName, Age FROM Tablename. This technique results in reduced disk I/O and better performance. |
Do not use SELECT * in your queries. |
An index will be used on where clauses and used for sorting, especially when tied to a limit.
|
Don’t add an index unless you actually need it. |
| Always use mysql_real_escape_string() function to prevent SQL injection. | Don’t trust on user input. It may be SQL injected. |
| If you’re storing a date, use a DATE or DATETIME field and don’t use TEXT data type. If you have to store maximum 255 character in a field then use VARCHAR. | Don’t use wrong data type for columns. |
| MySql allows you to use different engine types to store your data, each with its own advantages and drawbacks. The two main engines are MyISAM and InnoDB, and the differences between them affect much more than just performance. | Don’t pick wrong database engine for your data. |
| The prepared statement is the solution for this. When we prepare the query then MySql parse the common part of that query only one time. For example: Select * from products where productCode = ?; When MySQL executes this query with different question mark value, it does not have to parse the query fully. As a result, this helps MySQL execute the query faster, especially when MySQL executes the query multiple times. Because the prepared statement uses placeholders (?), this helps avoid many variants of SQL injection hence make your application more secure. |
Don’t execute a query without prepare statement. When we pass a MySql query then MySql parse that query and then execute it. MySql takes time in parsing each query. It slows the performance. |
| Use mysql_connect() and don’t forget to close it | Don’t use mysql_pconnect() in PHP. Persistent Connections are meant to reduce the overhead of re-creating connections to MySQL. When a persistent connection is created, it will stay open even after the script finishes running. Since Apache reuses it’s child processes, next time the process runs for a new script, it will reuse the same MySQL connection. It sounds great in theory. But this feature turns out to be not worth the trouble. You can have serious problems with connection limits, memory issues and so on. |
| NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. Unless you have a very specific reason to use a NULL value, you should always set your columns as NOT NULL |
Don’t Use NULL If possible. |
| Always take backup before deleting table or database. | Don’t delete anything before proper backup. |