There is a table Mysql InnoDB :

  • The table contains 22 columns, some of which can be NULL
  • The first column of the phone is required and always contains a phone number of 10 digits (any).
  • The phone column is not unique and there may be several different lines containing the same phone.
  • The table is static and does not imply adding rows.

  • The size of the table is around 500 million lines.

    How can I maximize the sample rate from the table by phone ? Will partitioning help me?

    1 answer 1

    The first optimization method is the formation of an index that is optimal for a specific query. Since the table is static, it is possible to create several different indexes in it that optimize the majority of queries to the table — there is no need to fear brakes when changing-replenishing-moving away from a large number of indexes. Accelerated price increased disk space.

    Partitioning in this case probably will not help. In any case, the effect of it will be penny, at the level of measurement accuracy. Except when everything happens on a system with a catastrophic lack of resources, primarily memory for the index cache.

    • And direct division of the table into several will help? - Zhenya40
    • What partitioning, what static division - one hell. Just that. that when partitioning is done by the server, you want to do it manually ... you think. will you get better? Well, if you divide the table in half, it will save you one iteration over the index when searching. If on 1000 parts - you will save 10 iterations. Neither one nor the other is stable in the benchmark (not unless there is a catastrophic lack of operatives). - Akina