Database is used to store information, we can say, this a collection of all necessary information/ data. Database stores all specific data into various tables which further divided in columns and rows in the particular table by an index number, which we can create, insert, update or retrieve as per our requirement. So, a database can store many tables and tables can store multiple data into particular rows and columns.
Here is an example of data stored in table named user.
Above is just an example for table in database, there are can be tons of data stored in such tables.
When we are working on a big project there are lots of information stored in the form of records, so we keep all the information or data on a different index number of particular table, there can be multiple tables with different type of data. A big database affects the speed or performance of the website.
For example; user or browser sends the request to server to get the information using a server side scripting language such as PHP
and at time server will find the desired data or information in database and return the information to user or browser.
This process consumes the time, which can be slow down the speed of the website. So here we can use some DATABASE OPTIMIZATION techniques for speed up the performance of any website or web application.
Requirement of Database Optimization: Now a days there are thousands of websites running and serving the same service to the client. So client have many options to choose the better one. So it is a big deal to attract and move to your website, I think the biggest deal to keep continues the client on your website. For this some things play good role like presentation of website, functionality of website and the performance of website.
Time is more precious than other things so website performance (speed) is play big role. If you have attractive features and functionality according to the client or customer but your website performance is slow then it is very difficult to hold the client on your website. So here are some important techniques to improve the performance of website:
Tips to Improve the Performance of Website
1. Keep minimum table: We stores all the information into table in database. When we get the information from the database first search the desired table and the get the particular information. To keep speed up your database do not make unnecessary tables.
2. Keep Indexed: Always keep your data indexed with primary key. It helps to speed up the database performance.
3. Type of data: Always keep clear in mind to set the data type to your data means specify INT or VARCHAR, INT is consumes less memory then VARCHAR on server.
4. Length of Data type : Always keep the relevant length to the data, it is very important to save the memory of server for example :
Above example shows of phone data, phone numbers keep only integer value there is no need to declare VARCHAR instead of INT. And here declare the length of the data is 255 whereas almost the phone numbers are keeps only 10 to 12 digits.
5. Selecting Query: Query helps to retrieve the data from the database. So choose the specified query to retrieve the data as
“SELECT * FROM users WHERE user = ‘mohan’”; Never use “SELECT *” it slow down the performance. Always use as
“SELECT FROM users WHERE user = ‘mohan’”;
6. USE LIMIT: Limit is specifies the how many records you want to retrieve from the database. Limit have two parameter (start, end) first one specify the starting address of the query for the desired data and second one is sets the end limit means how many records is being retrieve at a time from the database.
7. EXISTS() vs COUNT() : Always use EXISTS () instead of COUNT() because the COUNT() will scan entire table according to your matching result but the EXISTS() scan the table till than it not get the desire record and leave the rest. So performance wise the EXISTS() is faster than the COUNT().
8. Avoid unnecessary query: When a query runs on server, its consume memory and affect the speed of the website. For good practice and keep your code clean make sure run query only when you required.
9. Normalization and Demoralization: Normalization is the systems to divide the data into multiple tables to get overabundance and data unities. On the other hand Demoralization is process where the data from multiple tables are merged together in one table, so that data retrieval will be faster.
Use normalized tables when we need number of insert, update, delete operations are performed and joins of those tables are not expensive.
Use de-normalization where joins are expensive and frequent query is executed on the tables.
When we work on a big databases then performance and control of traffic of website become must; especially in public sector websites, shopping websites, online examination websites, then we need to handle the database optimization. To improve the performance of any website or web app, optimization of database is highly desirable along with logics in programmings. So, that is how we have come at the end of this blog, want to explore more about the PHP MySQL and database optimization
then go ahead and read our PHP Blogs
by our experts.