MySQL Federated index and WHERE clause optimization improve database running efficiency _mysql

Website system has been online so far, the amount of data has unwittingly up to 500M, nearly 8W recorded. The basics of database operations are becoming very slow, use people will feel manic ~ ~ Then put this situation in a group, including the machine configuration of what one said, immediately have a group of friends, and help me determine the problem is not machine configuration, "Shenzhen-Gunmen" Enthusiastic person his machine 512 memory data in the Hundred W also ran very fast, even with those few W-block machine like cattle (blown over), hehe ~ ~ ~

Under the analysis of the group of friends, try to put the sort, conditions such as a removal to do the test, the results found that the problem is in the sorting part, the removal of the sorting, execution time from the original 48 seconds into 0.3x seconds, this is what the level of change AH ~ ~ Look at this result I am excited ing ...

So I made a joint index of the fields involved in sorting, ALTER TABLE XX add index IndexName (x1,x2,x3), after 2 minutes to create a new index and then execute the same SQL statement, wow 0.28S .... Cool

And then follow the same line of thought to some of the other common SQL has been optimized, the effect immediately effective

After 30 minutes to check the slow SQL records file, not good, found that a good SQL became gray often slow, God horse situation?

After analyzing and testing the reason for adding a federated index, and having an or in the SQL statement, the problem is excluded when this or union is used.

This time to come to a learned: Write SQL when you do not one o'clock, casually write a OK, it will be thought to bring very serious consequences.

Attach a paragraph about the order in which the WHERE clause is executed:

In the use of MySQL query database, connected a lot of use, found very slow. For example:

SELECT ... WHERE p.languages_id = 1 and m.languages_id = 1 and c.languages_id = 1 and t.languages_id = 1 and p.products_id in (472,47 4)

The query takes more than 20 seconds, although indexes are established on each field. With analysis explain SQL analysis, it was found that tens of thousands of data were returned during the first analysis:

WHERE p.languages_id = 1, and then, in turn, narrow the range by condition.

And when I change the position of the where field, the speed is significantly improved:

WHERE p.products_id in (472,474) and p.languages_id = 1 and m.languages_id = 1 and c.languages_id = 1 and t.languages_id = 1

In this way, the first condition is p.products_id in (472,474), it returns less than 10 results, and then according to other conditions to filter, naturally in the speed of a larger increase.

After practice, do not think that the field in the where the order does not matter, you can put anywhere, should be as far as possible the first time to filter out most of the useless data, only to return the smallest range of data.

　　Hope to help friends who have the same experience.