Optimization of searches in MySQL

Optimization is a change of system with the purpose of increase of its{her} speed.

Optimization of job from a DB can be divided{shared} into 3 types:

* Optimization of searches

* Optimization of structure

* Optimization of the server.

Let's consider more in detail optimization of searches.

Optimization of searches - the type of optimization most simple and resulting{bringing} in the highest results.

SELECT

Searches which give in to optimization more often, searches about sample are.

To see as the search about sample operator EXPLAIN will be carried out is used:

http://www.mysql.com/doc/ru/EXPLAIN.html

With his  help we can see, in what order tables will communicate and what indexes thus will be used.

The basic mistake beginning{starting} is an absence of indexes on the necessary fields or creation of these on unnecessary fields. If you do{make} simple sample like:

SELECT * FROM table WHERE field1 = 123

That to you is necessary to put down an index on a field field1 if you use in sample a condition on two fields:

SELECT * FROM table WHERE field1 = 123 AND field2 = 234

That to you is necessary to create a compound index on fields field1, field2.

If you use connection of 2 or more tables:

SELECT *

FROM a, b

WHERE a.b_id = b.id

Or more generally:

SELECT *

FROM a

[LEFT] JOIN b ON b.id = a.b_id

[LEFT] JOIN with ON with id = b.c_id

That you should create indexes on fields on which will be tables will join. In this case it is fields b.id and c.id. However this statement is correct only in the event that sample will occur in that order in which they are listed in search. If, for example, optimizator MySQL will choose recordings from tables in the following order: c, b, a it will be necessary to put down indexes on fields: b.c_id and a.b_id. At linkage with help LEFT JOIN the table which goes in search at the left, will be always looked through by the first.

About syntax of creation of indexes it is possible to read in the documentation:

http://www.mysql.com/doc/ru/CREATE_INDEX.html

In more detail about use of indexes it is possible to read here:

http://www.mysql.com/doc/ru/MySQL_indexes.html

Sometimes there is such situation, that we constantly should do{make} samples of the same part of some very big table, for example, in many searches there is a connection with a part of the table:

[LEFT] JOIN b ON b.id = a.b_id AND b.field1 = 123 AND b.field2 = 234

In such cases can be reasonable bear{take out} this part in the separate time table:

CREATE TEMPORARY TABLE tmp_b TYPE=HEAP SELECT * FROM b WHERE b.field1 = 123 AND b.field2 = 234

And to work already with her (about time tables read in the documentation http: // www.mysql.com/doc/ru/CREATE_TABLE.html).

Also if we some times consider modular function for the same data for acceleration it is necessary to make such calculation separately and to put his  result in the time table.

Also there are brakes when people try « to catch in one search at once 2 hares », for example, at a forum phpclub'b the author of the following search asked, why he brakes:

SELECT f_m. *, MAX (f_m_v_w.date) AS last_visited, COUNT (DISTINCT f_b.id) AS books_num,

IF (f_m.region! = 999, f_r.name, f_m.region_other) AS region_name

FROM fair_members f_m

LEFT JOIN fair_members_visits_week f_m_v_w ON f_m_v_w.member_id = f_m.id

LEFT JOIN fair_regions AS f_r ON f_m.region = f_r.id

LEFT JOIN fair_books AS f_b ON f_b.pub_id = f_m.id

GROUP BY f_m.id

The author of search tries to count in one search the maximal value of attribute from one table and kol-in recordings in other table. In result it is necessary to attach 2 different tables which strongly slow down sample to search. For increase in speed of such sample it is necessary to bear{take out} calculation MAX'b or COUNT'b in separate search.

For calculation kol-va lines use function COUNT (*), c the instruction{indication} of "asterisk" as argument.

Why COUNT (*) usually is faster COUNT (id), I shall explain on an example:

There is a table message: id | user_id | text

With index PRIMARY (id), INDEX (user_id)

We should count up messages of the user with zadanym $user_id

Let's compare 2 searches:

SELECT COUNT (*) FROM message WHERE user_id = $user_id

And

SELECT COUNT (id) FROM message WHERE user_id = $user_id

For performance of the first search to us simply enough to be run on an index user_id and to count up kol-in the recordings satisfying a condition - such operation fast enough since, first, indexes at us are ordered and, second, often are in the buffer.

For performance of the second search we all over again it is passable on an index, for selection of recordings satisfying a condition then if recording gets under a condition we pull out her  (recording most likely will be on a disk) to receive value id and only then inkrimentim the counter.

In a result it is received, that at big kol-ve speed of the first search will be higher than recordings in times.

UPDATE, INSERT

Speed of inserts and obnovlenij in base depends on the size of inserted (updated) recording and from time of an insert of indexes. Time of an insert of indexes in turn depends on quantity{amount} of inserted indexes and the size of the table. This dependence can be expressed the formula:

[Time of an insert of indexes] = [kol-in indexes] * LOG2 ([the Size of the table])

At operations of updating under [kol-in indexes] those indexes at which there are updated fields are understood only.

Conditions in searches about updating are optimized the same as and in a case with samples.

At often change of some big table with a plenty of indexes it is meaningful to make inserts in other small auxiliary table with the same set of fields (but with absence of indexes) and periodically to throw the data from it{her} in the basic table, clearing auxiliary. Thus it is necessary to take into account, that the data will be deduced{removed} with lateness, that not always can be possible{probable}.

« To remove all the line long in the table, it is necessary to use TRUNCATE TABLE table_name. command » © documentation MySQL.