|
|
|
There multiple ways to optimize databases. You need a clear understanding of the structure of your database, what sort of platform it runs on, how much load is placed on it, and many other factors.
Relational Databases have a well-known and proven theory, a simple one that makes possible automatic cost-based query optimization, schema generation from high-level models and many other features that are vital. Relational database is collection of relations or tables. A table is a data organized in rows and columns. In a relational database all the data in a column should be of same data type. MySQL is a significant relational database model.
We will now walk through how a query analyzer solves a query. The major aim of a database is to solve the query but more than that, its goal is to execute the query with as few resources as possible. In database terms, this means reducing the number of I/O operations which goes to the disk. Disks are considerably slower than any other resource that the query optimizer has with it. Hence the query optimizer makes decision that is less costly from a disk I/O perspective.
The index is the relational database’s way of presorting the data into multiple perspectives by maintaining the details on the fields in the index and pointers where the actual data is stored. Clustered index are free from disk storage cost because it is the order the data is stored in the table. Except this, all other indices are associated with disk space cost. When using a database engine it performs two data reads, the first is to read the index to locate the pointer to the actual record. The second data read is to get the location specified by the pointer. This is a huge consideration for the database server when it tries to solve the query.
Statistics page is used by SQL server to determine to read an index. Each index provides the necessary information about the distribution of the index key values in the overall data of the table. The query optimizer needs to know how many rows will be returned to decide using the index. The lesser the rows the less likely it will be used by query optimizer. While using indices the field order is of immense concern. When planning indices, fields should be placed in the top of an index which is used frequently by queries. The query optimizer ignores even one field that isn’t part of the fields.
The WHERE clause, a part of the select statement plays a vital role in determining the index to be used. The WHERE clause filters the number of records displayed easing the query optimizer in using an index. This is typically used in matching a set of records. This allows the query analyzer to dramatically filter the number of reads that is to be done from the physical table to reduce the execution time of the query. Although matching is by far the most common type of entry, ranges are also requested in WHERE clauses. The query analyzer can decide from multiple individual statements the amount of the index that must be read and can decide from there if it makes sense to use the index or not.
ORDERBY clauses are used by query analyzer to facilitate the use of indexes. Covering indexes need more space in the database and require more time to update because of the longer key values; yet they can help query analyzer if they possess a static cross reference table. |
|
