The data in one of the MySQL tables has reached 20 million. How can you get closer to optimizing

The data in one of the MySQL tables has reached 20 million. How to get closer to optimization
The data in one of the MySQL tables has reached 20 million.
Data query is slow and slow, do not know how to improve?

The current approach is to do a lot of cache tables, but the data are not real-time.

I execute a statement like this. Select Max (ID) from Unit_keywords for 7 seconds, good machine performance, 64-bit, 4-core, 8G memory

------Solution--------------------
Check that the primary key and index are constructed correctly.

Do you use text as the primary key?

20 million though more, not 7 seconds ah.


------Solution--------------------
Check the MySQL configuration file.
Maybe your configuration is for a small database.
------Solution--------------------
2000W So, the performance is so high, look at the MySQL buffer settings

No, just a table.
------Solution--------------------
This problem should not be in the PHP version, in fact, you should find the DBA and the system architect to help you solve the problem.

Judging by the need for 7s from the Select Max (ID) from table, it is also possible to consider the mainstream practice of ID as a self-growing primary key, which means that your business encounters a lock competition. (This field if it is not a primary key or index, 20 million rows under the 7s returned to really say that your machine configuration is very good)
I think it's your business. There is also a SELECT, insert, and update operation for this table, and there may even be a delete. In fact, many businesses in pursuit of performance are using MySQL's MyISAM table and optimizing the business to keep only select and insert operations. This is because the insert does not need to lock the table, and the select is not interlocked. As for the problem of updating the data, it is entirely possible to update the new table by stripping the fields that need to be updated into the new table (which, of course, will be meaningless if the update operation is not too frequent).

In addition, the corresponding index is established according to the query clause of the business. MySQL's explain command can be a great reference.
Instead of caching the table, it is recommended that you implement it through additional caching services such as APC or memcache, and ensure that the cache is updated while the data is being updated.

It's not clear how your table is structured, it's just a common practice. If you can, it is recommended that you put the results of the show create table in the analysis.

