Database Optimization & Performance Analysis

You have made a web based application and the current user base is over 1 million now. You have already spent thousands of dollars in upgrading your servers to handle the increased load but the performance is still not at par with the expected one. Application database is the core of the application around which the whole application is framed and hence you must understand that the actual problem lies on how the database has been structured and how fast is it providing the required data to the application.

Database optimization is the need of the hour for you to improve your overall application performance. Database optimization is a database activity to make the database work in a more efficient manner so as to provider a faster execution of the application and this can be achieved by planning which database is most suitable for you, how the server should be configured to handle excessive data requests and how you need to write your search queries to get the results up to 20 times faster.

A proper planning is required to select the best suited database for your application depending op the number of records, available infrastructure and budget. Here is a comparative study of top three databases of the industry i.e. MySQL, Oracle and SQL Server.

There are many considerations need to be taken care of before choosing the appropriate database for the application. On one hand MySQL is a trusted open source database providing the lowest cost of ownership and on the other hand the databases like Oracle and SQL server are popular for saving you at performance critical situations especially in a Very large database situation.

Apart from planning the correct database for your organization, there are many ways in which the querying from the database can be optimized to a greater extent. Some of the major techniques employed while designing the application and creating the database are indexing the table fields, caching the results, etc.

Defining Indexes is one way in which the SELECT query results can be achieved upto 10 times faster.
A query “SELECT user from users” will return the list of the users by looking into the users table from top to bottom thus traversing every row in the table. If the user field in the table is indexed then the SELECT query will browse the table in a B-Tree fashion thus reducing the overall time in traversing by many folds.
However one should also take care while defining the indices for a table as the index use an overhead in terms of the memory used. For example a table with 1 billion records can uses as much as 16 GB of space to accommodate the indices.