Programmer Tips: 10 tricks to improve MySQL performance
Published on 13 May 15
James0
To start things of first, for those of you who don�t know, a Structured Query Language (SQL) is a standard for data storage and many websites have been using the ever popular MySQL software to add, access, and process data stored in a computer database. In fact, many web databases such as online shopping networks or image gallery websites may be using the MySQL software.
Here are the 10 tricks to improve your MySQL performance.
1) Do get an id field
One rule to mention is that for every table, ensure to place an id column. The primary key has to have Auto_increment, be unsigned, and have one form of INT. Furthermore, if you have a users table that possesses a unique username field, do not make that the primary key. It is known that VARCHAR fields as primary keys are slower. Also, you tend to have a better coding structure by referring to all your users with their id�s internally. An Id field will be even more beneficial if the database structure is too complex as the MySQL software can easily source the primary key field internally.
2) Create the Same Joined Column Types
Also, be mindful of the Index: There is a benefit in having your MySQL to be able to optimize JOIN operations. For instance, if you have an application that possesses multiple join queries, the columns will be indexed on both of your tables. Do ensure that the columns you join are of similar type, especially with the encoding for string columns. Otherwise, the MySQL software will try to attempt a full table scan. So if required to collect information from your tables, just amend your queries to fetch user information from specific columns.
3) Smaller Columns Are Faster
With database engines, disks can be a nuisance. Keeping columns tinier and more compressed would certainly boost performance and reduce the amount of disk transfer. MySQL docs have different Storage Requirements for different data types. If a table contains a few rows, you should make the primary key MEDIUMINT or SMALLINT instead of an INT.
4) Help Optimize Your Queries cache
Query caching is one of the most effective methods of improving database performance; hence they are usually enabled on MySQL servers. As the same query may run several times, it responds quicker to retrieve the results from a cache. Unfortunately, the CURDATE (current date) application has caused the Query cache function to be disabled. To apply query caching to a CURDATE query, all you have to do is add a line of PHP in front of the query, which will help you define the operator.
//CURDATE is not deterministic
$query = 'SELECT id FROM table WHERE publish_date = CURDATE()'; 5) Use of Prepared Statements
Since version 5.1 of MySQL, prepared statements were cached and supported. With prepared statements, your database will be granted with better performance and security. Prepared Statements will help to filter variables by default, which is important in protecting your system against SQL injection hacks. The advantages of prepared statements are more prevalent when using the same query multiple times in your application. You can set different values to the same prepared statement and MySQL will only have to parse it once.
Furthermore, newer editions of MySQL transmit prepared statements in binary format, which would assist to minimize network delays. In order to utilize prepared statements in PHP, you can check out the mysqli extension or use a database abstraction layer like PDO.
create a prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// bind parameters
$stmt->bind_param("s", $state);
// execute
$stmt->execute();
// bind result variables
$stmt->bind_result($username);
// fetch value
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
6) The benefits of Vertical Partitioning
Vertical Partitioning is the method used to split your table format vertically for optimization purposes. For example, you may have a users table that contains irrelevant email addresses that do not get viewed regularly. You may then be able to split such a table and store the email information on a separate table. This way your main users table will shrink in size.
As smaller tables have faster system performance, vertical portioning can shrink your main users� table size. Another example would be, if you have a "last login" field in your table, the table would update with every user login to the website. As such, every update on a table will cause the query cache for that table to be flushed. What you can do is to place that last login field into another table to reduce the number of updates every time a user login.
For both examples, do ensure that you join these 2 tables after attempting vertical partitioning for if you don�t, your database may experience a lag in performance.
7) Enabling consistent timing
Some of you may come across time zone values that vary depending on the platform you run MySQL on such as Windows (e.g. Xampp) or Unix (e.g. cPanel). For example, on cPanel VPS, you are unable to use time zone values such as Pacific/Auckland. But instead, you have to specify an offset from UTC time, which would affect the daylight saving time.
What you can do to achieve consistent timing for a cPanel VPS is to adjust your preferable server timing in two places (both in WHM). One in the Server Configuration and the other in PHP Configuration editor.
SELECT NOW() command in MySQL and an echo date (�m/d/Y h:i:s a, time())); in PHP.
8) Few tweaks to improve performance
You can alter several settings to experience greater performance from your MySQL server. One way is to increase your memory allocations. You have to set the key buffer that is shared across the whole server to 40% system ram. After which, you can increase the query caches to make them stay slightly longer. This would allow connection buffers to be set at nice values.
9) Add an EXPLAIN command word to SELECT queries
An easy and convenient tip! When adding the EXPLAIN keyword into your MySQL software, it will provide a clearer elaboration of what your query is doing. Next step, if you encounter a rather complicated SELECT query with joins, simply type EXPLAIN in front of the entire query. A rather comprehensive result page will be shown in a table form. With that being explained, such a technique may grant you plenty of time and convenience.
10) Backup multiple databases
For various purposes, you can also backup multiple databases in MySQL by entering the MySQLdump command such as
mysqldump �u[user name] �p[password] [database name 1] [database name 2] .. > [dump file}
Well folks those are my ten tricks you can apply for your MySQL software. If you have other programmer tips and tricks regarding MySQL, do leave a comment below and share it!
You may also be interested in
This blog is listed under
Open Source
, Development & Implementations
, Data & Information Management
and Operating Systems
Community
Related Posts:
Post a Comment
