There is a large (in my opinion) MYSQL table (19 fields). Even the query SELECT COUNT (*) FROM table_name; It takes 6 minutes, which does not suit me very much. It is necessary to arrange the indices so as not to spoil the case finally and quickly make samples for several specific fields (approximately 5-7 of 19). Is it possible to add indexes to an already filled table, and how long will it take?

  • can depend on the sucking - retvizan
  • 5-7 fields for search is quite a lot. It is necessary to carefully look at all applicable queries, which ones are often used, which are not. perhaps some queries can be somehow changed so that they can use the same indices. Although there is a primary key on the table, count () should go on the index, did it work on it for 6 minutes ... - Mike
  • | id | int (11) | NO | PRI | NULL | auto_increment | - Alexander

2 answers 2

Add an index on a large table

You can always add an index. The question is, what happens to an outsider. Depending on the storage engine and the version of the DBMS.

mysql/innodb to 5.6 when adding an index will block the table for writing. Those. all insert , update , delete queries will wait for the end of the index creation. select can work. Starting from 5.6 - index creation is possibly competitive.

If you are on the old version, then the classic trick was to increase replication, then create an index on the slave, wait for the slave to catch up with the master, switch the slave into the new master. The same trick should work with any storage engine.

How much time it takes to create an index - depending on the amount of data and the piece of iron on which mysql .

count

Just do not use count on large sets of strings. For analytics - read separately. For heavy analytics, a normal solution is a separate piece of hardware with database replication only for analytics calculation requests.

Or count the county separately, radish, or column subd. Or triggers, if you need to read the amount more often than to write.

how to arrange indexes

Need to learn a specific application. Universal can not say anything.

What to do with a large sign

Mysql out of the box has support for partitioning . Some restrictions are on use, for example, foreign keys not supported. As a rule, almost all requests from the application to large labels have a kind of common filter. For example, 90% of requests only want data for the last month, or are almost always tied to the user id. Perhaps the data in your plate can be cut into partitions.

    Several options for development: