Browse All Articles > Tips to Enhance your MySQL database performance
Hi Developers,
Hope you all are doing good at your end.
Today I am going to explain what the key points you need to keep in your mind while using MySQL database for high scale applications.
We all know Database is the backbone of any application. In web based applications we have to consider the database uses as everything on a network to share with its users.
Coming to the point... what things will help you to enhance your Web application's performance:
1. Select only what you need.
Yes in a web development environment you must have to fetch several information from database to display them onto the page. So keep this point in your mind to while using "SELECT" queries. People are used to "SELECT * FROM my_table" but we should avoid this approach until all columns of the tables are required to be select, better using "SELECT id, name, location, blah, blah FROM my_table" this will make your system faster (just a bit) as database will not use its CPU to fetch all the unwanted columns from database, specially while using JOINS. Also means that the table might have added columns but wont upset processing your query.
2. Use Indexing for your Table and its Columns.
Table indexes in MySQL are Primary, Unique, Index and Full-text, we can use them as our need.
Let me tell you how Indexes will help to enhance the performance of your web application. Indexes are keeping the data into parts suppose you have 10,00,000 records in any table if you have used indexes for the same table the data will indexed as 1-100 in IND_X_1, 101-200 in IND_X_2 and so on, its depends on the MySQL configuration.
So each time your querying data from the same table like "SELECT id, name FROM users where id = 198" and you have indexed the column id then it will only search the record with id 198 into its corresponding index which is 101-201 and so on, its seeks to the same index to find the same information rather search will start with 1st row into the table.
3. Use of LIMIT.
Have you ever think why LIMIT have a role while searching the records from the database. Let me explain this to you. Suppose you have so many matching records into your database but you only requires only few of them, use LIMIT MySQL will stop it search as it found the total mentioned number or rows and it will save a huge CPU uses and volume of network traffic.
4. Avoid Queries into Loop.
We are used to putting our queries into loop while programming in PHP, better if we get all the desired id's and then Put into IN(1,2, 3, and so on) it will also save CPU uses to parse the multiple queries.
5. Avoid Sub-Queries.
Sub-queries will take more time to execute, better if we use multiple JOIN according to our need. Please note JOIN will treated as a single query in MySQL server where each Sub-Query is teared as a new query.
6. Avoid Full Wildcards for matching records.
If there is any possibility to achieve the same search parameter with low level Wild cards such as %name or name% rather then %name% then we should use the same as matching multiple records will use extra MySQL CPU.
7. MySQL Caching.
MySQL caching can preserve the result into a cache file and next time when you query the same records it will returns directly from its cache rather then executing the same into database. AdoDB, Memcached and CSQL Cache will help you to understand the concept behind the MySQL cache and its uses.
I hope you will find the above helpful for you while developing an application using MySQL database.
Hope you all are doing good at your end.
Today I am going to explain what the key points you need to keep in your mind while using MySQL database for high scale applications.
We all know Database is the backbone of any application. In web based applications we have to consider the database uses as everything on a network to share with its users.
Coming to the point... what things will help you to enhance your Web application's performance:
1. Select only what you need.
Yes in a web development environment you must have to fetch several information from database to display them onto the page. So keep this point in your mind to while using "SELECT" queries. People are used to "SELECT * FROM my_table" but we should avoid this approach until all columns of the tables are required to be select, better using "SELECT id, name, location, blah, blah FROM my_table" this will make your system faster (just a bit) as database will not use its CPU to fetch all the unwanted columns from database, specially while using JOINS. Also means that the table might have added columns but wont upset processing your query.
2. Use Indexing for your Table and its Columns.
Table indexes in MySQL are Primary, Unique, Index and Full-text, we can use them as our need.
Let me tell you how Indexes will help to enhance the performance of your web application. Indexes are keeping the data into parts suppose you have 10,00,000 records in any table if you have used indexes for the same table the data will indexed as 1-100 in IND_X_1, 101-200 in IND_X_2 and so on, its depends on the MySQL configuration.
So each time your querying data from the same table like "SELECT id, name FROM users where id = 198" and you have indexed the column id then it will only search the record with id 198 into its corresponding index which is 101-201 and so on, its seeks to the same index to find the same information rather search will start with 1st row into the table.
3. Use of LIMIT.
Have you ever think why LIMIT have a role while searching the records from the database. Let me explain this to you. Suppose you have so many matching records into your database but you only requires only few of them, use LIMIT MySQL will stop it search as it found the total mentioned number or rows and it will save a huge CPU uses and volume of network traffic.
4. Avoid Queries into Loop.
We are used to putting our queries into loop while programming in PHP, better if we get all the desired id's and then Put into IN(1,2, 3, and so on) it will also save CPU uses to parse the multiple queries.
5. Avoid Sub-Queries.
Sub-queries will take more time to execute, better if we use multiple JOIN according to our need. Please note JOIN will treated as a single query in MySQL server where each Sub-Query is teared as a new query.
6. Avoid Full Wildcards for matching records.
If there is any possibility to achieve the same search parameter with low level Wild cards such as %name or name% rather then %name% then we should use the same as matching multiple records will use extra MySQL CPU.
7. MySQL Caching.
MySQL caching can preserve the result into a cache file and next time when you query the same records it will returns directly from its cache rather then executing the same into database. AdoDB, Memcached and CSQL Cache will help you to understand the concept behind the MySQL cache and its uses.
I hope you will find the above helpful for you while developing an application using MySQL database.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)