Indexing Strategies for Optimizing Queries on MySQL

MySQL’s index begins by reviewing how indexes work, as well as their structure. Next, it reviews indexing features specific to each of the major MySQL data storage engines. This article then examines a broad range of situations in which indexes might help speed up your application. In addition to examining how indexes can be of assistance. MySQL’s index usage type: B-trees, hash and bitmap, in order to optimize queries, although MySQL has implemented and indexes spacious R-trees. The index type corresponds to the particular kinds of internal algorithms and datastructures used to implement the index. In MySQL, support for a particular index type is dependent upon the storage engine.

Database performance becomes an important issue in the presence of large amounts of data, complex queries, queries manipulating large amounts of data, long running queries, queries that lock every one else out, large number of simultaneous transactions, large numbers of users and limited bandwidth. In general, most database systems are designed for good performance. The best improvements can be achieved in the initial design phase but sometimes not enough information is available about the characteristics of a database. Later, altering a large database in production use can be expensive and practical considerations put constraints on what can be changed. Tuning can make the difference between a query taking milliseconds or minutes or even more to execute.

Database system is the core of management information systems, database-based online transaction processing (OLTP) and online analytical processing (OLAP) is a banking, business, government and other departments of the most important one of computer applications. From the application of most systems, the query operation in a variety of database operations in the largest occupied, and the query operation is based on the SELECT statement in the SQL statement is a statement of the cost of the largest. For example, if the amount of data accumulated to a certain extent, such as a bank account to the database table of information on the accumulation of millions or even tens of millions of records, full table scan often requires tens of minutes time, and even a few hours. If better than the full table scan query strategy can often be reduced to a few minutes to make inquiries, we can see the importance of query optimization technology.

Query optimization is a DBMS (database management system) tasks, prepared with the programmer has little to do with SQL statement, which is wrong. A good query plan performance often can improve the number of times. Query plan is submitted by users a collection of SQL statements, query plan is optimized to deal with the statement after the collection of produce. DBMS query plan to deal with the process is as follows: in the query after the lexical, syntax check, the statement will be submitted to the DBMS’s query optimizer, optimizer after algebraic optimization and optimization of access to the path followed by pre-compiled modules processing of statements and generate inquiries, planning, and then at the right time to the system implementation, the final results will be returned to the user. In the actual database products (such as Oracle, Sybase, etc.) are all versions of the high cost-based optimization method, this optimization of the dictionary from the system based on the information table to estimate the different costs of planning inquiries, and then select a better planning. While it is in the database query optimization has been done better, but by the user of the SQL statement submitted to the system based on optimization, it is difficult to imagine a worse original query plan after the system has become efficient after optimization, so written statement of the advantages and disadvantages of users is essential.

MySQL’s optimizer always tries to use the information at hand to develop the most efficient query plans. However, requirements change over time; users and applications can introduce unpredicted requests at any point. These requests might include new transactions, reports, integration, and so forth.

Indexing is the most important tool you have for speeding up queries. Other techniques are available to you, too, but generally the one thing that makes the most difference is the proper use of indexes. On the MySQL mailing list, people often ask for help in making a query run faster. In a surprisingly large number of cases, there are no indexes on the tables in question, and adding indexes often solves the problem immediately. It doesn’t always work like that, because optimization isn’t always simple. Nevertheless, if you don’t use indexes, in many cases you’re just wasting your time trying to improve performance by other means. Use indexing first to get the biggest performance boost and then see what other techniques might be helpful.

The particular details of index implementations vary for different MySQL storage engines. For example, for a MyISAM table, the table’s data rows are kept in a data file, and index values are kept in an index file. You can have more than one index on a table, but they’re all stored in the same index file. Each index in the index file consists of a sorted array of key records that are used for fast access into the data file.

By contrast, the BDB and InnoDB storage engines do not separate data rows and index values in the same way, although both maintain indexes as sets of sorted values. By default, the BDB engine uses a single file per table to store both data and index values. The InnoDB engine uses a single tablespace within which it manages data and index storage for all InnoDB tables. InnoDB can be configured to create each table with its own tablespace, but even so, a table’s data and indexes are stored in the same tablespace file. MySQL uses indexes in several ways. As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause or rows that match rows in other tables when performing joins. For queries that use the MIN() or MAX() functions, the smallest or largest value in an indexed column can be found quickly without examining every row. MySQL can often use indexes to perform sorting and grouping operations quickly for ORDER BY and GROUP BY clauses.

Sometimes MySQL can use an index to reading all the information required for a query. Suppose that you’re selecting values from an indexed numeric column in a MyISAM table, and you’re not selecting other columns from the table. In this case, when MySQL reads an index value from the index file, it obtains the same value that it would get by reading the data file. There’s no reason to read values twice, so the data file need not even be consulted. In general, if MySQL can figure out how to use an index to process a query more quickly, it will. There are and disadvantages. There are costs both in time and in space. In practice, these drawbacks tend to be outweighed by the advantages, but you should know what they are.

First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. Second, an index takes up disk space, and multiple indexes take up correspondingly more space. This might cause to reach a table size limit more quickly than if there are no indexes: For a MyISAM table, indexing it heavily may cause the index file to reach its maximum size more quickly than the data file. For BDB tables, which store data and index values together in the same file, adding indexes causes the table to reach the maximum file size more quickly.

All InnoDB tables that are located within the InnoDB shared tablespace compete for the same common pool of space, and adding indexes depletes storage within this tablespace more quickly. However, unlike the files used for MyISAM and BDB tables, the InnoDB shared tablespace is not bound by your operating system’s file-size limit, because it can be configured to use multiple files. As long as you have additional disk space, you can expand the tablespace by adding new components to it. InnoDB tables that use individual tablespaces are constrained the same way as BDB tables because data and index values are stored together in a single file.

Advertisements

, , , , , , , , , , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: