Data table performance optimization
A computer system to optimize a database is provided. A processor determines a frequency of search for a plurality of columns in a table. A processor determines a length of the plurality of columns. A processor determines a rank for the plurality of columns based, at least in part, on both the frequency of search and the length of the plurality of columns. A processor generates a first set of combinations of the plurality of columns. A processor determines an optimization of the first set of combinations of the plurality of columns. A processor selects an optimal combination of the first set of combinations, wherein the optimization of the optimal combination is greater than the optimization for all other combinations in the first set of combinations. A processor moves the plurality of columns excluded from the optimal combination.
1. A computer system comprising:
one or more computer processors;
one or more computer readable storage media; and
program instructions stored on the computer readable storage media for execution by at least one of the one or more processors, the program instructions comprising:
program instructions to determine a frequency of search for a plurality of columns in a table;
program instructions generate at least one prefix value column corresponding to a column of the plurality of columns in the table, wherein the prefix value column has a prefix length shorter in length than the column of the plurality of column in the table;
program instructions to determine a rank for the plurality of columns based on (i) the frequency of search, (ii) the length of the plurality of columns, and (iii) the prefix length of the at least one prefix value column;
program instructions to generate a first set of combinations of the plurality of columns, wherein the first set of combinations includes the at least one prefix value column;
program instructions to generate a second set of combinations of the plurality of columns, wherein the second set of combinations is based, at least in part, on the first set of combinations and at least one additional column from the plurality of columns excluded from the first set of combinations;
program instructions to determine a first optimization ratio of the first set of combinations of the plurality of columns, wherein the first optimization ratio is based, at least in part, on (i) the frequency of search for the plurality of columns included in the first set of combinations and (ii) a page utilization of the plurality of columns comprising the at least one prefix value column included in the first set of combinations;
program instructions to determine a second optimization ratio of the second set of combinations of the plurality of columns, wherein the second optimization ratio is based, at least in part, on (i) the frequency of search for the plurality of columns included in the second set of combinations and (ii) a page utilization of the plurality of columns comprising the at least one prefix value column included in the second set of combinations;
program instructions to select the first set of combinations or the second set of combinations as an optimal combination of the plurality of columns, wherein the optimal combination is based, at least in part, on the greater value of the first optimization ratio of the first set of combinations and the second optimization ratio of the second set of combinations;
program instructions to add a pointer to the optimal combination of the first set of combinations, wherein the pointer includes the location of the plurality of columns excluded from the optimal combination; and
program instructions to move the plurality of columns excluded from the optimal combination to a separate storage device than a storage device storing the plurality of columns in a table.