Data table performance optimization
A method 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 method of optimizing a database, the method comprising: determining, by one or more processors, a frequency of search for a plurality of columns in a table;
generating, by the one or more processors, 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 columns in the table;
determining, by the one or more processors, 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;
generating, by the one or more processors, a first set of combinations of the plurality of columns, wherein the first set of combinations includes the at least one prefix value column;
generating, by the one or more processors, 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;
determining, by the one or more processors, 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;
determining, by the one or more processors, 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;
selecting, by the one or more processors, 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;
adding, by the one or more processors, 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
moving, by the one or more processors, 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.