Automatic index recommendation for improved query performance

Abstract

To provide systems and methods suitable for providing automatic index recommendations for improved database query performance.SOLUTION: A method includes: identifying candidate indexes associated with a database query that is classified as a slow query; generating a feature vector for each candidate index to represent statement features and statistical features associated with the candidate index; obtaining an index recommendation value for each candidate index via one or more machine learning models; and presenting an index recommendation to a user device. The index recommendation identifies a first index of the candidate indexes based at least partially on the index recommendation value obtained for the first index. The method also includes, in response to receiving a user input indicative of a user selection of the first index, updating a database schema to include the first index.SELECTED DRAWING: Figure 6

Description

[Technical Field]

[0001] The subject matter disclosed herein relates generally to the use of indexes to improve database query performance. More particularly, but not exclusively, the subject matter relates to systems and methods suitable for providing automated index recommendations. [Background technology]

[0002] Indexes are an important tool for addressing slow queries in database systems. A database query can be classified as a "slow query" if it is flagged for attention, for example, because its execution time exceeds a predetermined threshold (e.g., the database query takes longer than one second to execute) or based on user complaints about its execution time.

[0003] Creating indexes can reduce the number of slow queries and thereby improve database query performance, but indexes should be selected carefully because they can introduce additional overhead into the database system. Identifying an appropriate index can be a difficult or time-consuming task, especially in large, dynamic database systems where multiple indexes can potentially be implemented in an attempt to address slow queries. Moreover, once an appropriate index is identified, evaluating its performance and integrating it into the database schema can pose technical challenges. Summary of the Invention [Means for solving the problem]

[0004] One aspect of the present disclosure provides a system, comprising: at least one memory for storing instructions; one or more processors configured with instructions to perform the operations; The operation is as follows: identifying a plurality of candidate indexes associated with database queries classified as slow queries; generating, for each candidate index of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector to one or more machine learning models to obtain an index recommendation for each candidate index of a plurality of candidate indexes; triggering, at the user device, presentation of an index recommendation identifying a first index of a plurality of candidate indexes, the first index being identified in the index recommendation based at least in part on an index recommendation value obtained for the first index; receiving a user input indicating a user selection of a first index; automatically updating the database schema to include the first index in response to receiving user input; Includes: [Brief explanation of the drawings]

[0005] Some examples are shown in the accompanying drawing figures, for purposes of explanation and not limitation. The drawings are not necessarily drawn to scale, and like numbers may describe similar components in different views or examples. To more easily identify discussion of any particular element or operation, one or more most significant digits in a reference number will refer to the figure number in which that element is first featured.

[0006] [Figure 1] 1 is a diagrammatic representation of a network environment including a database management system and an index recommendation system, according to some examples. [Figure 2] FIG. 1 is a block diagram of some components of a database management system, according to some examples. [Figure 3] 1 is a block diagram of some components of an index recommendation system, according to some examples. [Figure 4] 1 is a flowchart illustrating operations of a method suitable for training a machine learning model to generate index recommendations, according to some examples. [Figure 5] FIG. 1 illustrates a database query and several tables to illustrate aspects of index vectorization performed to generate training data, according to some examples. [Figure 6] 1 is a flowchart illustrating the operation of a method suitable for automatically generating index recommendations for addressing slow queries, according to some examples. [Figure 7] 1 is a schematic diagram of the generation of index recommendations according to some examples, where the index recommendation system utilizes three different machine learning models to generate index recommendations. [Figure 8] FIG. 1 illustrates the training and use of a machine learning program, according to some examples. [Figure 9] FIG. 1 is a block diagram illustrating a software architecture for a computing device, according to some examples. [Figure 10] 1 is a block diagram of a machine, in the form of a computer system, according to some examples, upon which instructions may be executed to cause the machine to perform any one or more of the methodologies discussed herein. DETAILED DESCRIPTION OF THE INVENTION

[0007] Establishing appropriate indexes can be an effective way to reduce the occurrence of slow queries in a database system. The systems and methods described herein enable the automatic generation of index recommendations for improving database query performance. The exemplary techniques described herein enable the automatic analysis of slow queries, the generation of appropriate index recommendations, and the integration of selected indexes into a database schema, thereby providing a targeted technical solution for performance improvement.

[0008] In some examples, slow queries are analyzed using one or more machine learning models. The one or more machine learning models may be trained using vectorized data that represents characteristics of existing or suitable indexes. After training, values generated by the machine learning models during inference may be used to generate index building plans that are likely to improve database query performance.

[0009] An example method may include identifying a plurality of candidate indexes associated with a database query classified as a slow query. The terms "database query" and "query" are used interchangeably in the context of this disclosure.

[0010] Prior to identifying multiple candidate indexes, a slow query may be detected or classified. For example, the system may detect that a query execution time for a database query exceeds a threshold, and in response to detecting that the query execution time associated with the database query exceeds the threshold, designate the database query as a slow query (e.g., by flagging a record of the slow query in the database). In some examples, the designation of the database query as a slow query automatically triggers further action, such as obtaining index recommendations for candidate indexes, as described below.

[0011] A database query classified as a slow query may be one of multiple database queries monitored using an index recommendation application. For example, the index recommendation application may monitor the query execution time of each of multiple database queries to determine whether to flag one or more of the queries as slow queries. In some examples, the index recommendation application is implemented as a microservice that automatically monitors and triggers the generation of index recommendations for database queries that are found to meet one or more requirements associated with slow queries.

[0012] In some examples, a feature vector is generated for each candidate index associated with the slow query. The feature vector may represent one or both of statement features and statistical features associated with the candidate index. For example, the statement features may indicate the location of each of one or more candidate index columns of the candidate index within the database query. The statement features may encode such locations with an encoding scheme (e.g., using one-hot encoding) that indicates the presence or absence of each query condition involving the candidate index column.

[0013] The statistical features in the feature vector may include at least one of a selectivity value associated with the database query, a cardinality value associated with the database query, a number of null values in each of one or more candidate index columns of the candidate index, or a number of rows associated with each of the one or more candidate index columns. The method may include parsing the database query (e.g., with a Structured Query Language (SQL) parser) to extract information from the database query. A syntactic analysis operation may be performed on the information to obtain statement features. A statistical analysis may be performed on the information to obtain statistical features.

[0014] The method may include providing the feature vectors generated for the candidate indexes to one or more machine learning models to obtain an index recommendation value for each candidate index. The index recommendation value may include, for example, a likelihood score. The system may automatically present the index recommendations at the user device. The index recommendation may identify one or more indexes from the plurality of multi-column candidate indexes for selection, approval, or modification by the user.

[0015] In some examples, the index recommendation identifies at least a first index of the plurality of candidate indexes based at least in part on an index recommendation value obtained for the first index. The method may include receiving a user selection of the first index. In response to receiving the user input, the database schema may be automatically updated to include the first index.

[0016] The method may further include assessing performance of the first index according to a predetermined performance metric prior to presenting the index recommendation at the user device. The performance metric may relate, for example, to an improvement or expected improvement in execution time associated with implementing the first index.

[0017] Assessing the performance of the first index may include determining a first query execution time for the database query without applying the first index, determining a second query execution time for the database query by applying the first index, and comparing the first query execution time to the second query execution time. In some examples, to assess the performance of a candidate index based on a performance metric, the candidate index is run on a subset of data from the database associated with the database query. Such a subset of data is referred to herein as a "sample library." The first index may then be selected based at least in part on the performance of the first index.

[0018] In some examples, the system causes presentation of index recommendations at the user device within a user interface that presents the index recommendations along with an indication of the performance of the first index. In this manner, the user may be given automatically generated options for selecting an index while simultaneously being enabled to review the expected improvement associated with the index selection. This may enable the user, for example, to make a more informed decision by assessing the expected improvement against other factors, such as increased overhead, prior to enabling integration of the selected index.

[0019] The multiple candidate indexes may include indexes of different types. For example, in a columnar storage context, at least one of the multiple candidate indexes may be a multi-column candidate index (e.g., a two-column index or a three-column index), in which case a feature vector for the multi-column candidate index may be generated by generating column-specific values indicative of characteristics of individual columns in the multi-column candidate index, generating multi-column values indicative of composite characteristics of the individual columns in the multi-column candidate index, and combining the column-specific values and the multi-column values.

[0020] In some examples, the plurality of candidate indexes includes a first subset and a second subset, where the first subset includes one or more single-column candidate indexes and the second subset includes one or more multi-column candidate indexes. As a specific, non-limiting example, the plurality of candidate indexes may include a first subset of one or more single-column candidate indexes, a second subset of one or more two-column candidate indexes, and a third subset of one or more three-column candidate indexes.

[0021] The one or more machine learning models may include a separate machine learning model trained to process feature vectors for each type of candidate index. For example, the one or more machine learning models may include a first machine learning model trained to process each feature vector of one or more single-column candidate indexes and at least one second machine learning model trained to process each feature vector of one or more multi-column candidate indexes. As a specific, non-limiting example, the system may implement a single-column model to process feature vectors for single-column candidate indexes, a two-column model to process feature vectors for two-column candidate indexes, and a three-column model to process feature vectors for three-column candidate indexes.

[0022] In some examples, the system may automatically recommend at least one candidate index for each type of candidate index. For example, if the candidate indexes include a first subset, a second subset, and a third subset, the system may automatically recommend the highest-scoring candidate index from each subset based on their respective index recommendation values.

[0023] Each machine learning model may be trained using a dataset including sample feature vectors, each labeled to indicate whether a sample index corresponding to the sample feature vector exists in the sample database. A positive label may thus indicate, for example, a desirable index or an index associated with improved performance. The machine learning model may, for example, be a binary classification model trained to predict the presence or absence of an index based on the feature vector. In some examples, the machine learning model may output a predicted label and a confidence score (as an example of an index recommendation value).

[0024] The examples described herein may address or alleviate one or more technical problems. At least some technical problems with database management systems may stem from or relate to a reliance on manual index management by database managers or developers. Manual index management may include, for example, manual analysis of query patterns or data access paths and manual creation of indexes to optimize query performance. These processes are not only time-consuming but also prone to human error, especially in complex databases with large data volumes and high query variety.

[0025] A slow query may involve multiple columns of a database table, and a developer may need to analyze and test various possible indexes (e.g., various single-column and multi-column options) to determine which indexes are likely to address the problem. With large, complex, or interconnected database tables, it may be difficult or impractical for a developer to identify optimal or near-optimal indexes without expending excessive time or computational resources.

[0026] Such technical issues can be addressed or alleviated by providing an automated index recommendation system that leverages machine learning algorithms to efficiently analyze query and index data. The system described herein can automatically identify indexing opportunities and suggest optimal or near-optimal indexes without the need for manual intervention. This can reduce errors and ensure that databases are constantly or periodically optimized for query workloads.

[0027] Technical issues can also stem from inefficient analysis of query performance as part of determining the need to index or select appropriate indexes. Database management systems may lack the ability to process and analyze vast amounts of query execution data in real time, leading to delayed or suboptimal index adjustments. This inefficiency can result in persistent performance bottlenecks when the system may not be able to quickly adapt to changing data access patterns or may miss subtle but impactful optimization opportunities.

[0028] The examples described herein provide technical solutions for overcoming inefficiencies by incorporating one or more performance monitoring components that may automatically evaluate query performance or the impact of candidate indexes on query performance. This may enable the system to dynamically suggest and integrate index modifications, thereby increasing the throughput of the database system.

[0029] Index management may also suffer from technical issues regarding scalability and adaptability across diverse and evolving datasets. For example, as data volume increases and new types of queries are introduced, it may be difficult to efficiently scale and adapt indexes. For example, in the context of SQL queries, indexes can improve performance for “select” queries but may introduce overhead for “action” queries because the index itself needs to be updated when data changes. Such issues may become increasingly worse as datasets grow or evolve. By providing technical solutions for recommending appropriate indexes based on feature vectors that constitute various aspects of queries and their performance, more appropriate or effective indexes may be surfaced. This may lead to a better balance between read efficiency and write efficiency (e.g., by avoiding having too many indexes that may slow down “action” queries, but still having high-performance indexes to speed up “select” queries).

[0030] A further technical challenge may be how to integrate user interactivity and feedback into the index establishment process while still allowing for substantially automated index recommendation and integration. The examples described herein seamlessly or nearly seamlessly incorporate user interaction, reducing the risk of human error or delays in performance improvement, while still allowing users to exercise control over some aspects of the process.

[0031] Examples described herein may improve the operation of a database system by automatically surfacing appropriate index recommendations or automatically integrating selected indexes into a database schema, thereby leading to improved system performance (e.g., as a result of faster query execution times). When the effects of this disclosure are considered as a whole, one or more of the methodologies described herein may eliminate the need for some effort or resources that would normally be involved in a database management system. Computational resources used by a system, database, or network may be used more efficiently or reduced, for example, as a result of a reduction in the computational resources required to perform query log analysis or to perform a near-exhaustive examination of a large number of candidate indexes. Examples of such computational resources may include processor cycles, network traffic, memory usage, data storage capacity, power consumption, and cooling capacity.

[0032] 1 is a diagrammatic representation of a networked computing environment 100 in which some examples of the present disclosure may be implemented or deployed. One or more servers in a server system 104 provide server-side functionality over a network 102 to network-connected devices, in the illustrative form of user devices 108, accessed by users 106. A web client 112 (e.g., a browser) or a programmatic client 110 (e.g., an "app") may be hosted and executed on the user devices 108.

[0033] An application program interface (API) server 124 and a web server 126 provide respective programmatic and web interfaces to the components of the server system 104. An application server 122 hosts a database management system 128, an index recommendation system 130, and a user interface system 132, each of which includes one or more components, modules, or applications. It will be appreciated that the database management system 128, the index recommendation system 130, and the user interface system 132 may in other examples be distributed across multiple application servers.

[0034] The user device 108 can communicate with the application server 122, for example, via a web interface supported by the web server 126 or via a programmatic interface provided by the API server 124. While only a single user device 108 is shown in FIG. 1 , it will be appreciated that in some examples, multiple user devices may be communicatively coupled to the server system 104. Furthermore, although some functions may be described herein as being implemented in either the user device 108 (e.g., the web client 112 or the programmatic client 110) or the server system 104, whether to locate particular functionality in the user device 108 or the server system 104 may be a design choice.

[0035] The application server 122 is communicatively coupled to a database server 134 to facilitate access to one or more information storage repositories, such as a database 136. In some examples, the database 136 includes a storage device that stores information to be processed by the database management system 128, the index recommendation system 130, or the user interface system 132.

[0036] Application server 122 accesses application data (e.g., application data stored by database server 134) and provides one or more applications or software tools to user device 108 via web interface 140 or app interface 142. According to an example, and as described further below with particular reference to FIGS. 2-7, application server 122 may use database management system 128 to provide one or more tools or functions for database management, including query processing, and may use index recommendation system 130 to provide one or more tools or functions for the automated generation of index recommendations.

[0037] The database management system 128 is responsible for storing and organizing data. The database management system 128 may handle multiple tasks, such as data retrieval, insertion, update, and deletion operations. The database management system 128 may be implemented using a variety of database models, including, but not limited to, relational, NoSQL, or object-oriented databases. The database management system 128 may ensure data integrity and security while providing efficient access, for example, via a structured query language or other database-specific query mechanism. In some examples, the database management system 128 is designed to handle large amounts of data and support concurrent access by multiple users or systems.

[0038] The index recommendation system 130 is responsible for analyzing database queries and recommending optimal or near-optimal indexing strategies to improve query performance. Using advanced algorithms, such as machine learning techniques, the index recommendation system 130 may process vectorized data representing details of slow queries to provide index recommendations. The index recommendation system 130 may also interface with the database management system 128 to implement or manage selected indexes, thereby reducing query latency and improving the overall efficiency of the database management system 128.

[0039] The user interface system 132 enables users, such as the user 106 of the user device 108, to interact with the database management system 128 or the index recommendation system 130. The user interface system 132 may provide a gateway via the API server 124 or the web server 126, allowing database administrators and users to visualize, manage, and manipulate data and system configurations. The user interface system 132 may provide a user interface with an intuitive layout and a control or command interface for technical users (e.g., via the web interface 140 or the app interface 142). For example, the user interface system 132 may cause the display of index recommendations generated by the index recommendation system 130 for review or approval by the user 106. The user interface may include a dedicated section for index management, where the user can view, for example, active index recommendations, decision history, and performance metrics.

[0040] In some examples, users 106 use database management system 128 to query database 136. Database management system 128 may implement a query procedure or query optimizer to speed up data retrieval.

[0041] The index recommendation system 130 then operates to generate recommendations for new indexes to integrate with the database 136. In some examples, the index recommendation system 130 runs as a microservice within the architecture shown in FIG. 1. The index recommendation system 130 may monitor query statistics and detect slow queries (e.g., queries that take longer than one second to execute). This can automatically trigger the process of generating index recommendations, which are then presented to the user 106 at the user device 108 by the user interface system 132. Additionally, if selected, the index recommendation system 130 may cause automatic integration of the selected index into a database schema managed by the database management system 128.

[0042] In some examples, to assess the performance of an index or candidate index, the index recommendation system 130 uses a sample library 138. The sample library 138 is a repository that stores a subset of data from an actual database (e.g., database 136) to which a query is related. The sample library 138 may include representative data samples or synthetic data that mimic the characteristics of the full dataset. For example, the sample library 138 may provide a "miniature" version of the database 136 that substantially reflects the distribution of values and table relationships. By applying an index to the sample library 138 and measuring query performance improvements, the index recommendation system 130 can estimate or predict the potential impact of index changes on the actual database while reducing the computational load associated with performance testing.

[0043] The index recommendation system 130 may cooperate with a machine learning system 144 to perform machine learning model training or inference. In some examples, the machine learning system 144 provides the machine learning pipelines and models used to generate index recommendations. The machine learning system 144 may provide various capabilities, such as training models, performing inference, and monitoring performance.

[0044] The machine learning system 144 may provide automated machine learning capabilities that can generate a pipeline tailored to a given process or use case. The machine learning system 144 may determine aspects such as appropriate data preprocessing, feature engineering, model algorithms, and hyperparameters to use. The machine learning system 144 may utilize training datasets (e.g., training datasets including sample representations stored in the database 136) to intelligently build custom machine learning pipelines. The pipelines provided by the machine learning system 144 may then be used to train, evaluate, and select models for deployment. This automation may eliminate or reduce the need for time-consuming manual development or refinement of machine learning solutions.

[0045] In some examples, application server 122 is part of a cloud-based platform offered by a software provider that enables users 106 to use the tools of database management system 128 and index recommendation system 130. For example, an account holder such as user 106 may perform queries, manage data in database 136, and select indexes to integrate into database 136.

[0046] One or more of application server 122, database server 134, API server 124, web server 126, database management system 128, index recommendation system 130, and user interface system 132 may each be implemented, in whole or in part, in a computer system, as described below with respect to FIG. 10. In some examples, an external application, such as external application 118 executing on external server 114 or external application 120 executing on external server 116, may communicate with application server 122 through a programmatic interface provided by API server 124. For example, a third-party application may support one or more features or functions on a website or platform hosted by a third party, or may implement some methodology and provide input or output information to application server 122 for further processing or publication.

[0047] Network 102 may be any network that enables communication between machines, databases, and devices. Thus, network 102 may be a wired network, a wireless network (e.g., a mobile or cellular network), or any suitable combination thereof. Network 102 may include one or more portions that make up a private network, a public network (e.g., the Internet), or any suitable combination thereof.

[0048] In some examples, database 136 is a multi-model database that stores data in main memory rather than secondary storage such as disk storage or solid-state drives. For example, database management system 128 may provide access to database 136 having a column-oriented, in-memory database design. In addition to providing database 136, database management system 128 may enable users to access advanced capabilities such as search, retrieval, analytics, and data integration capabilities.

[0049] 2 is a block diagram illustrating some components of a database management system 128, according to some examples. The database management system 128 is shown to include a transaction handling component 202, a data modeling component 204, a storage management component 206, a data integration and replication component 208, a security and user management component 210, a backup and restore component 212, a performance optimization component 214, an auditing and monitoring tools component 216, and an index management component 218. A user 106 may use a user device 108 to access or interact with the components of the database management system 128 via a user interface system 132 of FIG. 1.

[0050] The transaction handling component 202 handles database-related transactions, such as database queries, modifications, and other user requests. The transaction handling component 202 is responsible for ensuring the consistency and reliability of transactions executed within the database 136. The transaction handling component 202 manages the atomicity, consistency, isolation, and durability (ACID) properties of database transactions, ensuring that database operations are processed reliably and without interference from concurrent transactions. For example, the transaction handling component 202 may use locking mechanisms or multi-version concurrency control to maintain data integrity.

[0051] In operation, a user 106 may enter a database query via a user device 108. The database query is sent over the network 102 and then handled by the transaction handling component 202. The transaction handling component 202 may be responsible for, for example, parsing and executing SQL queries. The transaction handling component 202 may examine the query for accuracy, create an execution plan, use indexes (if any), and perform the required actions based on the user instructions. As further described below, the transaction handling component 202, in conjunction with a performance optimization component 214, may analyze various execution strategies for the query and select one with the lowest estimated cost based on factors such as data size, indexes, join conditions, and system resources. The transaction handling component 202 may also be responsible for processing the results and returning the results to the user 106.

[0052] The data modeling component 204 allows the user 106 to create and manage data structures such as tables, views, and schemas. The data modeling component 204 may support various data modeling paradigms, such as relational and dimensional modeling, allowing for the efficient design of data stores that align with business requirements and query patterns.

[0053] The storage management component 206 handles the allocation, organization, and optimization of data storage. For example, the storage management component 206 may manage storage between on-premise storage and cloud storage. As mentioned above, in some cases, the database 136 may provide in-memory columnar storage. However, in other cases, the database 136 may provide different types of storage, such as a division between in-memory storage and secondary storage, which division may be facilitated by the storage management component 206. The storage management component 206 may also be responsible for data compression, partitioning, and other storage optimization techniques that increase system performance and reduce the storage footprint.

[0054] The data integration and replication component 208 is configured to ensure the synchronization and movement of data within and across different environments. The component 208 enables the integration of data from various sources, ensuring that the database 136 reflects the most up-to-date and accurate information. The data integration and replication component 208 may facilitate data replication for purposes such as disaster recovery, load balancing, or data warehousing. For example, the data integration and replication component 208 may replicate data from an operational database to an analytical system to support real-time business intelligence without impacting the transactional workload.

[0055] The security and user management component 210 operates to manage access control and protect the database management system 128 and the database 136 from unauthorized access. For example, the security and user management component 210 is responsible for one or more of user authentication, authorization, role-based access control, and auditability.

[0056] The backup and restore component 212 ensures data durability and recoverability. The backup and restore component 212 may provide the user 106 with a mechanism to create backups of the database 136 at regular intervals or in response to specific events. For example, the component 212 may support point-in-time restore, allowing the database to be restored to a specific moment before a failure occurred.

[0057] The performance optimization component 214 provides tools for improving the performance (e.g., query execution speed) of the database 136. For example, the performance optimization component 214 may work with the transaction handling component 202 to perform query optimization, in-memory calculation optimization, and automatic tuning of database parameters. The performance optimization component 214 also manages indexes created to facilitate data retrieval or otherwise improve query performance.

[0058] The index management component 218, as shown in FIG. 2, is a subcomponent of the performance optimization component 214 and facilitates management of indexes. The index management component 218 may allow the user 106 to create new indexes, modify existing indexes, or delete indexes. The index management component 218 may provide tools for analyzing query patterns and automatically adjusting indexes to match changing data access patterns. In some examples, the index management component 218 communicates with the index recommendation system 130 to integrate recommended indexes surfaced by the index recommendation system 130 into the database 136.

[0059] The supervision and monitoring tools component 216 provides tools for a database supervisor to manage and govern the database system. For example, a user 106 may use the features of the supervision and monitoring tools component 216 to perform system configuration, performance monitoring, troubleshooting, and maintenance operations. The supervision and monitoring tools component 216 may work in conjunction with the user interface system 132 to provide a dashboard for real-time monitoring of system health and alert the supervisor to potential problems.

[0060] 3 is a block diagram illustrating some components of an index recommendation system 130, according to some examples. The index recommendation system 130 is shown to include a data collection component 302, a feature extraction component 304, a vector generation component 306, a model interface component 308, a recommendation analysis component 310, a user interaction component 312, an index integration component 314, and a performance monitoring component 316.

[0061] The data collection component 302 is responsible for collecting data items used to generate index recommendations or to monitor or assess performance. For example, the data collection component 302 may collect query logs, execution statistics, or database schema information. In some examples, the data collection component 302 collects data about columns involved in queries to identify potential indexing opportunities. The data collection component 302 may be used to collect data for training a machine learning model (e.g., as described with reference to FIG. 4 or FIG. 5). The data collection component 302 may also or alternatively be used to collect data for use in performing inference with a trained model.

[0062] The feature extraction component 304 is configured to extract useful features from the data items. For example, the feature extraction component 304 may receive the data collected by the data collection component 302 and perform a statistical analysis to obtain values of statistical features (e.g., cardinality or selectivity) associated with the index or a syntactic analysis to obtain values of statement features associated with the index (e.g., to identify whether a column participates in a range condition, an equality condition, or some other query condition).

[0063] The vector generation component 306 is responsible for generating feature vectors. For example, the vector generation component 306 can receive data collected by the data collection component 302, some of which may have been processed by the feature extraction component 304, and generate vectors in a structured format to represent features of candidate indexes. For example, the vector generation component 306 can encode aspects of the data into a machine-readable format that can be processed by a machine learning algorithm. In some examples, the vector generation component 306 represents the presence or absence of certain SQL operations in a query as binary vectors or vector components, and also encodes statistical features such as selectivity and cardinality values. The vector generation component 306 can thus convert unstructured or raw data into structured data in a format that can be directly used by the machine learning component.

[0064] The model interface component 308 serves as an interface between components of the index recommendation system 130 and one or more machine learning models (e.g., models executed by the machine learning system 144 of FIG. 1 ). In some examples, the model interface component 308 is used to feed feature vectors to the one or more models and retrieve index recommendations generated by the one or more models. In some examples, the model interface component 308 manages input and output data flow for at least one machine learning model trained with a dataset including sample feature vectors, each labeled to indicate whether a sample index corresponding to the sample feature vector exists in a sample database.

[0065] The recommendation analysis component 310 is configured to process the output of the machine learning model and formulate actionable index recommendations. For example, the recommendation analysis component 310 may analyze the index recommendation values (e.g., likelihood scores associated with predicted labels) generated by the machine learning model for the candidate index and determine whether to suggest the candidate index to a user.

[0066] In some examples, the recommendation analysis component 310 evaluates the performance or expected performance of a candidate index prior to triggering a candidate index suggestion to a user. For example, the recommendation analysis component 310 may determine an expected improvement in query execution time associated with implementing a candidate index and surface a candidate index suggestion only if the expected improvement exceeds a threshold. The recommendation analysis component 310 may operate or communicate with the performance monitoring component 316 in this regard.

[0067] The user interaction component 312 communicates with the user interface system 132 to ensure that index recommendations are presented to a user (e.g., to the user 106 via the user device 108). For example, the user interaction component 312 may cause the presentation of an index recommendation including one or more candidate indexes and, optionally, an indication of expected performance or performance improvement associated with each candidate index. The user interaction component 312 may receive user input and automatically trigger the implementation or adjustment of an index based on the user input.

[0068] For example, the user interaction component 312 may cause a user interface (e.g., web interface 140) on the user device 108 to present three recommended indexes. The user 106 may then select one of the indexes. In response to receiving the user selection, the user interaction component 312 triggers integration of the index into the database schema, as described elsewhere herein.

[0069] The index integration component 314 may automate the implementation of the approved or selected index. For example, the index integration component 314 may automatically update the database schema to include the selected index based on detecting user approval. For example, the index integration component 314 may automatically execute the necessary SQL commands to create a new index or adjust the properties of an existing index.

[0070] The performance monitoring component 316 is configured to assess the impact of the index on database system performance. For example, the performance monitoring component 316 may be designed to monitor performance metrics or indicators to evaluate the effectiveness of the index and provide feedback on the success of the recommendation. The performance monitoring component 316 may enable a feedback loop to facilitate continuous improvement of the index recommendation system 130.

[0071] In some examples, at least some of the components shown in Figure 2 or 3 are configured to communicate with each other to implement aspects described herein. One or more of the components described herein may be implemented using hardware (e.g., one or more processors of one or more machines) or a combination of hardware and software. For example, a component described herein may be implemented by a processor configured to perform the operations described herein for that component. Moreover, two or more of these components may be combined into a single component, or functionality described herein for a single component may be sub-divided among multiple components. Furthermore, according to various examples, the components described herein may be implemented using a single machine, database, or device, or distributed across multiple machines, databases, or devices.

[0072] 4 is a flowchart illustrating operations of a method 400 suitable for training a machine learning model to generate index recommendations, according to some examples. By way of example and not limitation, aspects of method 400 may be implemented by one or more components, devices, systems, networks, or databases shown in FIG. 1, FIG. 2, or FIG. 3.

[0073] Indexes are commonly used to improve database query performance. For example, an SQL query (e.g., a “select” query that retrieves data from a database using one or more query conditions) can retrieve data faster by traversing an index tree rather than performing a full table scan, thereby reducing query execution time and computational resource consumption. At a high level, method 400 aims to automate the generation of index recommendations based on the observation that slow queries, potentially due to the lack of appropriate indexes, may not be specific to a single database but may exhibit patterns that are common across different databases. Thus, a successful indexing strategy applied to one database may potentially be replicated to achieve performance improvements in other databases. In other words, if some queries are slow in one context due to missing indexes, similar queries in another context (e.g., queries with similar statement or statistical characteristics) may benefit from the creation of comparable indexes.

[0074] The method 400 begins at a start loop element 402 and proceeds to operation 404, where the index recommendation system 130 collects data regarding various queries, existing indexes, and potential indexes. Operation 404 may involve the collection of data from various databases or other sources by the data collection component 302. The data may include query logs and metadata of existing indexes, such as the table or tables to which the index belongs, the column or columns the table contains, and statistical data (e.g., row count, data distribution, selectivity, cardinality, or the presence of null values). In some examples, the index recommendation system 130 obtains data about existing indexes from the index management component 218 of the database management system 128.

[0075] Method 400 proceeds to operation 406, where index recommendation system 130 performs index vectorization (e.g., using vector generation component 306). Index vectorization may be performed to enable a machine learning model to identify characteristics of existing indexes and learn the relationship between those characteristics and the expected performance gains that result from indexing.

[0076] In some examples, the feature extraction component 304 of the index recommendation system 130 extracts values for statement features and statistical features of each index to be included in the training dataset, and the vector generation component 306 constructs a feature vector for that index based on the statement features and statistical features. The indexes to be included in the training dataset may include both real indexes (e.g., indexes that exist in one or more databases) and non-existent indexes (e.g., indexes that could be created but have not been applied to any of the databases from which data items are collected). Because the indexes form part of the training dataset, they may be referred to as sample indexes.

[0077] A query may include query conditions used to filter, group, aggregate, or order data items. For each sample index, statement features may include characteristics that describe the syntactic role of one or more columns in the corresponding query. For example, statement features include information about how columns are used in various SQL clauses and with aggregate functions. Statement features may provide insight into the context in which a column is queried, which can influence decisions about whether and how to index a column. For example, if the sample index is a single-column index, its statement features describe the location of the column in the query, such as whether the column is part of a "JOIN" clause.

[0078] Statistical features may be, for example, quantitative attributes that describe the data distribution and usage statistics of one or more columns in a database. By reflecting underlying data characteristics, statistical features may provide insight into the potential effectiveness of indexes on query performance. Thus, statistical features may provide a useful indication of what the impact of an index on a particular column or combination of columns may be in a database.

[0079] Table 1 below shows and describes non-limiting examples of statement features and statistical characteristics. For example, the selectivity described in Table 1 can be an important statistical feature. Selectivity provides a measure of the "uniqueness" of a column value, with higher selectivity potentially indicating greater potential performance gains from indexing. For example, a column containing only one value, such as a primary key, has a selectivity of 1 (or 100%), which is the highest possible selectivity. On the other hand, a column in which many rows have the same value has a selectivity closer to 0 (or closer to 0%), indicating that an index on this column may be less beneficial to query performance. An index may still provide some benefit in the latter case, but the performance gain may be less significant.

[0080] [Table 1]

[0081] A feature vector may be generated based on the statement features and statistical features associated with the index for a sample query. For example, consider a sample query involving columns A, B, and C of a table, referred to in this example as COLUMN_A, COLUMN_B, and COLUMN_C, respectively. To generate a feature vector for each possible single-column index, the statement features associated with each column may be encoded (e.g., using one-hot encoding), and the statistical feature values may be summed to obtain the feature vector. In some examples, the statistical features may be normalized, for example, so that each value in the feature vector is greater than or equal to 0 and less than or equal to 1.

[0082] Features of a two-column index, such as index(COLUMN_A,COLUMN_B), may be formed by concatenating the features of the single-column indexes, index(COLUMN_A) and index(COLUMN_B). Additionally, common or composite statistical feature values, such as common or composite cardinality values or common or composite selectivity values when considering both columns, may be added as one or more additional statistical features so that the feature vector of a two-column index more fully describes its underlying statistics. A similar approach may be followed to assemble a feature vector for a three-column candidate index, index(COLUMN_A,COLUMN_B,COLUMN_C).

[0083] To enable supervised learning, a label may be added to each feature vector. For example, if index(COLUMN_A) already exists in the operational database, a positive label may be added. On the other hand, if index(COLUMN_B) does not exist, the index may be labeled with a negative label. In other words, the feature vector for index(COLUMN_A) may then be a positive vector (+1), and the feature vector for index(COLUMN_B) may then be a negative vector (−1). In other examples, other criteria may be used for labeling. For example, if a sample index indicates that it improves query performance beyond a predetermined threshold, that feature vector may be labeled positively, and other samples may be labeled negatively.

[0084] 5 is a diagram 500 illustrating a database query in the exemplary form of a sample query 502 and multiple tables to illustrate aspects of index vectorization performed to generate training data, according to some examples. Sample query 502 is an SQL statement involving two tables (CUSTOMER and VENDOR) and five columns, as shown in FIG.

[0085] Method 400 of Figure 4 and diagram 500 of Figure 5 consider single-column indexes, two-column indexes, and three-column indexes, although it will be appreciated that in other examples, fewer types of indexes may be considered (e.g., only single-column indexes or only single-column and two-column indexes) or a greater number of index types may be considered (e.g., four-column indexes may be considered).

[0086] To perform index vectorization in the case of Figure 5, a feature vector is generated for each possible index of each of the three types. This includes the five single-column indexes shown in Figure 5 (for C_A, C_B, C_C, C_D, and V_A), as well as all possible two-column index combinations (not shown in Figure 5), and all possible three-column indexes. Only two examples of three-column indexes (for the combination of C_A, C_B, and C_C, and the combination of C_A, C_B, and C_D) are shown in Figure 5. Figure 5 therefore shows examples of single-column index vectorization 504 and multi-column index vectorization 506.

[0087] For each feature vector, statement features are encoded using one-hot encoding. For example, for a single-column index on column C_A, one-hot encoding is used to indicate that the column participates in the "EQUAL" query condition but not in the "JOIN" or "RANGE" query conditions. It will be appreciated that the statement features shown in Figure 5 are non-limiting examples and that additional or alternative statement features may be included.

[0088] Additionally, for each feature vector, statistical features are added to the feature vector. For example, for a single-column index on column C_A, its cardinality and selectivity values for sample query 502 are added. It will again be appreciated that the statistical features of Figure 5 are non-limiting examples and that additional or alternative statistical features may be included.

[0089] As mentioned above, for multi-column indexes, the feature vector may be expanded to include not only the individual statement and statistical feature values of the constituent columns, but also values that represent composite features. As shown in Figure 5, the individual statement feature values may be concatenated, and the statistical feature values may be added to the feature vector. For example, for a three-column index, the selectivity value may be based on the combined uniqueness of the three columns.

[0090] The feature vector is also labeled to mark each sample index as a positive sample (+1) or a negative sample (-1) based on whether the sample index corresponds to an actual or existing index in the collected data. Thus, the full feature vector for sample index(C_A) is [0, 1, 0, 500, 0.8, -1], and the full feature vector for sample index(C_A, C_B, C_C) is [0, 1, 0, 0, 1, 0, 1, 0, 0, 1000, 0.95, +1]. A positive label for sample index(C_A, C_B, C_C) may indicate that the index is beneficial to query performance, and a negative label for sample index(C_A) may indicate that it will not be beneficial (or not sufficiently beneficial) to query performance.

[0091] 4, the feature vectors obtained during operation 406 are then used as training data for training one or more machine learning models. By including both positive and negative examples in the training data, the machine learning models may learn to distinguish between valid and invalid indices.

[0092] Method 400 then proceeds to operation 408, which includes selecting a machine learning algorithm. After collecting sufficient training data (by way of example, and not limitation, 10,000 or 15,000 training samples may be obtained for each machine learning model to be trained), it is necessary to choose an appropriate machine learning algorithm for training. In the case of FIG. 4, the Category Boosting (CATBoost) algorithm is selected. CATBoost is based on gradient boosting of decision trees. Note that this algorithm is a non-limiting example and that other algorithms may be utilized. For example, another algorithm for training a binary classification model, such as extreme gradient boosting (XGBoost), may be used.

[0093] At operation 410, machine learning models are trained with the training data. In some examples, the machine learning system 144 of FIG. 1 is used to perform or facilitate the training. In the case of FIG. 4, a separate binary classification model is trained for each type of index. In other words, a first machine learning model is trained to process single-column feature vectors, a second machine learning model is trained to process two-column feature vectors, and a third machine learning model is trained to process three-column feature vectors.

[0094] During the training process for each model, the input data is the feature vector of the sample index, and the predicted value is the label corresponding to that feature vector. Thus, the machine learning model is trained to predict whether a feature vector has a positive or negative label and outputs a probability score along with the predicted label. The model learns by adjusting its internal parameters to minimize the difference between its prediction and the actual label. Through repeated training on a large and diverse set of feature vectors, the model is able to generalize from the training data to predict the usefulness of new, unseen indexes.

[0095] Each trained model can then output an index recommendation value for a candidate index. The index recommendation value (e.g., a probability score) may indicate the likelihood that the index will improve performance of the slow query or the likelihood that the index will not improve performance of the slow query. In some examples, method 400 includes validating the machine learning model at operation 412. This may ensure that the model performs well or satisfactorily on unseen data. Common validation metrics include precision, recall, and F1 score (the latter providing a single score that balances precision and recall).

[0096] The trained machine learning model is integrated into the index recommendation system 130 at operation 414. For example, the model interface component 308 of the index recommendation system 130 may be communicatively coupled to the machine learning system 144 such that the feature vector may be automatically communicated to the machine learning system 144 for inference by the appropriate trained model. The method 400 ends at an end loop element 416.

[0097] Thus, in some examples, the index recommendation system 130 can be deployed not only to recognize when a query is unsuccessful, but also to suggest appropriate indexes based on learned patterns from a wide range of databases. The index recommendation system 130 may use insights from "big data" and the predictive power of machine learning to recommend indexes that are tailored to increase the speed or efficiency of database queries.

[0098] Figure 6 is a flowchart illustrating the operation of a method 600 for automatically generating index recommendations for addressing slow queries, according to some examples. By way of example and not limitation, aspects of method 600 may be implemented by one or more components, devices, systems, networks, or databases shown in Figures 1, 2, or 3. Furthermore, method 600 will be described with reference to the non-limiting example shown in Figure 7. Figure 7 is a diagram 700 illustrating the generation of index recommendations, according to some examples, in which index recommendation system 130 utilizes three different machine learning models to generate index recommendations (e.g., as described in method 400 of Figure 4).

[0099] Method 600 begins at start loop element 602 and proceeds to operation 604, where index recommendation system 130 detects that the query execution time of a database query exceeds a threshold. For example, index recommendation system 130 may use one or both of data collection component 302 and performance monitoring component 316 to examine database queries made by a user (e.g., user 106 of FIG. 1 ) to determine whether the database queries are executing quickly enough.

[0100] For example, the threshold may be one second, and the index recommendation system 130 flags database queries with query execution times greater than one second as slow queries. In some cases, the index recommendation system 130 may look at the average query execution time to make this determination.

[0101] In some examples, the user 106 or supervisor enables the feature for index recommendations in the context of the database management system 128 and the database 136. The index recommendation system 130 may run as a microservice that monitors the database queries of the user 106 and automatically triggers index recommendations for detected slow queries to improve the performance of the database management system 128 and the database 136. The index recommendation system 130 may automatically surface its index recommendations for review or approval by the user, as described in more detail below.

[0102] 7, a slow query 702 is shown by way of example only. The slow query 702 involves three columns (A, B, and C) from a table (TEST). The index recommendation system 130 can detect the slow query 702 and automatically trigger the acquisition of index recommendations to address the slow query 702.

[0103] 6, method 600 includes identifying candidate indexes associated with slow query 702. Index recommendation system 130 automatically identifies that three candidate indexes may be generated for each column (A, B, and C), three additional candidate indexes may be generated for each pair of columns (AB, BC, and AC), and another candidate index may be generated for the combination of all three columns (ABC). Thus, for chart 700, operation 606 involves identifying seven different candidate indexes associated with slow query 702.

[0104] Then, in operation 608, the index recommendation system 130 generates a feature vector for each of the candidate indexes using the vector generation component 306. For example, using the feature engineering process described with reference to Figures 4 and 5, the index recommendation system 130 can perform vectorization 704 to obtain three single-column feature vectors 706, three two-column feature vectors 708, and one three-column feature vector 710, as shown in Figure 7.

[0105] In some examples, each feature vector represents both statement features and one or more statistical features for the respective candidate index. For example, the single-column feature vectors 706 may each have the format [JOIN, EQUAL, RANGE, ORDER_BY, CARDINALITY, SELECTIVITY], with the feature vector for candidate_index(A) being [0, 1, 0, 0, 500, 0.6]. In this case, the statement features are encoded using one-hot encoding to encode the existence of the equivalent query condition into the slow query 702, and the statement features are added as the final two vector values.

[0106] As described elsewhere, for two-column feature vector 708 and three-column feature vector 710, each feature vector may include both individual feature vector values for each column and values representing composite characteristics of the columns. Thus, index recommendation system 130 may generate column-specific values (e.g., individual statement feature values) that indicate characteristics of the individual columns in the multi-column candidate index, as well as multi-column values (e.g., statistical feature values for the composite column) that indicate composite characteristics of the individual columns in the multi-column candidate index, and combine or concatenate the column-specific values and multi-column values to form the feature vector. By way of example only, a three-column feature vector 710 for candidate_index (ABC) may have the format: [JOIN_A, EQUAL_A, RANGE_A, ORDER_BY_A, JOIN_B, EQUAL_B, RANGE_B, ORDER_BY_B, JOIN_C, EQUAL_C, RANGE_C, ORDER_BY_C, COMBINED_CARDINALITY_A_B_C, COMBINED_SELECTIVITY_A_B_C].

[0107] The feature vectors are then fed to machine learning models to obtain index recommendations in operation 610. In Figure 7, the index recommendation system 130 uses a separate machine learning model for each type of candidate index. Specifically, and as shown in Figure 7, during inference 712, a single-string model 714 is fed with the single-string feature vector 706, a two-string model 716 is fed with the two-string feature vector 708, and a three-string model 718 is fed with the three-string feature vector 710.

[0108] For each candidate index, the output of inference 712 is one or more index recommendation values. For example, for each candidate index, index recommendation system 130 may receive a predicted label and a probability value or confidence score from machine learning system 144. As described with reference to FIG. 4, the training process may involve using labeled data, where each feature vector in the training dataset is associated with a label that indicates whether the associated index is positive (e.g., beneficial to query performance) or negative (e.g., not beneficial or not implemented).

[0109] The outputs of the single-string model 714, the two-string model 716, and the three-string model 718 are analyzed. As shown in Figure 7, an analysis 720 is performed by the index recommendation system 130 (e.g., using the recommendation analysis component 310) on the single-string recommendation value 722 generated for each single-string feature vector 706, the two-string recommendation value 724 generated for each two-string feature vector 708, and the three-string recommendation value 726 generated for the three-string feature vector 710. For example, the index recommendation system 130 may analyze the index recommendation values to determine whether the predicted label is positive and, for candidate indexes with positive predicted labels, what the probability value or confidence score is.

[0110] 7, index recommendation system 130 selects one candidate index corresponding to one of single-column feature vectors 706 (e.g., the highest-scoring, positively labeled candidate index), one candidate index corresponding to one of two-column feature vectors 708 (e.g., the highest-scoring, positively labeled candidate index), and a candidate index (e.g., assumed to have a positive predictive label) corresponding to three-column feature vector 710. Thus, index recommendation system 130 generates recommended index 728, recommended index 730, and recommended index 732 corresponding to the single-column index, the two-column index, and the three-column index, respectively.

[0111] Table 2 below provides illustrative examples of single-column recommended values 722, two-column recommended values 724, and three-column recommended values 726. Based on the values in Table 2, index recommendation system 130 may, for example, select candidate index candidate_index(A) as recommended index 728, candidate index candidate_index(AC) as recommended index 730, and candidate index candidate_index(ABC) as recommended index 732. For example, if the model output for candidate index candidate_index(ABC) indicated a negative label or a probability score less than 50%, index recommendation system 130 could have selected only recommended index 728 and recommended index 730.

[0112] [Table 2]

[0113] The index recommendation values generated by the trained models 714, 716, and 718 are then used by the index recommendation system 130 to determine recommendations. As mentioned, each model may predict a label and a probability score. However, it will be appreciated that in other examples, a model may be trained to output only a probability score. For example, a threshold (such as 0.5 or 50%) can be set to classify candidate indexes with probabilities above the threshold as positive (recommended) and those below the threshold as negative (not recommended).

[0114] In some examples, and referring again to FIG. 6, method 600 includes evaluating at least a subset of the candidate indexes at operation 612. While more indexes can often lead to better performance for slow queries, there can also be drawbacks or problems associated with increasing the number of indexes linked to a database. For example, more indexes can increase overhead and storage space requirements. Additionally, the indexes recommended by index recommendation system 130 may be ineffective or slow.

[0115] To this end, before surfacing a final recommendation, index recommendation system 130 may evaluate the performance of one or more of the candidate indexes. For example, index recommendation system 130 may use recommendation analysis component 310 or performance monitoring component 316 to test recommended index 728, recommended index 730, and recommended index 732 against sample library 138 of FIG. 1.

[0116] To assess each candidate index, the index recommendation system 130 may examine whether the candidate index is expected to result in improved query execution time based on consulting the sample library 138. For example, the index recommendation system 130 may compare a first query execution time measured without using the candidate index to a second query execution time measured using the candidate index (in both cases against the sample library 138) to verify that response time is improved or expected to be improved.

[0117] Note that query execution time, or improvement in query execution time, is one example of a performance metric that may be used in operation 612. Other performance metrics or factors may also be considered, such as changes in storage or costs associated with consolidating indexes.

[0118] The index recommendation system 130 may select at least one of the candidate indexes based on, for example, the index recommendation value and the results of the performance evaluation. For example, the index recommendation system 130 may test the performance of the recommended index 728, the recommended index 730, and the recommended index 732 and determine that the recommended index 728 and the recommended index 730 (rather than the recommended index 732) should be recommended to the user. For example, the index recommendation system 130 may determine that the recommended index 732 is ineffective or does not sufficiently improve query execution time, and then select only the recommended index 728 and the recommended index 730 as its final recommendations. In other words, the index recommendation system 130 may perform a first filtering process to arrive at an initial set of recommendations, and then use the output of the trained models 714, 716, and 718 to perform a performance assessment as a second filtering process to arrive at a final set of recommendations to surface.

[0119] 6, method 600 proceeds to operation 614, where index recommendation system 130 (e.g., using user interaction component 312 and via user interface system 132 of FIG. 1) causes presentation of one or more of the candidate indexes at a user device (e.g., user device 108 of user 106) as part of the index recommendation at operation 614. For example, as described with reference to FIG. 7, index recommendation system 130 may present recommended indexes 728 and recommended indexes 730 at user device 108 in a user interface of database management system 128 or index recommendation system 130.

[0120] In some examples, the recommended index is presented (e.g., in web interface 140 or app interface 142 of FIG. 1 ) along with additional data, such as performance evaluation results (e.g., “Recommend index [INDEX_NAME]. This index is estimated to lead to a 35% improvement in query execution time.”) or potential resource impact (e.g., an indication of storage space requirements associated with the recommendation), thereby enabling user 106 to make an informed decision regarding the recommended index.

[0121] At operation 616, the index recommendation system 130 receives a user selection of a candidate index. For example, the user 106 may use the user device 108 to review the recommendations and select a recommended index 730 corresponding to one of the two-column candidate indexes for implementation. In response to the user selection, the index recommendation system 130 automatically updates a database schema (e.g., the schema of the database 136 of FIG. 1 ) to include the selected index.

[0122] As part of operation 618, the index recommendation system 130 may cooperate with the index management component 218 of the database management system 128 to update the database schema to reflect the existence or details of the new index. For example, the index management component 218 may update the database schema to include or update an index definition. The index definition may indicate one or more of the name of the index, the table or tables to which the index belongs, and the column or columns that the index covers. The index definition also specifies the type of index (e.g., B-tree, hash, or full-text) and whether the index is unique or non-unique. The updated database schema may then be used by the database management system 128 to modify the database structure, which may involve automatically executing a data definition language (DDL) statement such as “CREATE INDEX” to physically implement the index in the database. The method 600 concludes at an end loop element 620.

[0123] In view of the above-described implementations of the subject matter, the present application discloses the following list of examples, where one feature of an example in isolation or multiple features of an example, when taken in combination, and optionally in combination with one or more features of one or more additional examples, constitutes a further example that is also within the scope of the present application disclosure.

[0124] Example 1 is a system comprising at least one memory that stores instructions and one or more processors configured by the instructions to perform operations, the operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; generating, for each of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector to one or more machine learning models to obtain an index recommendation value for each of the plurality of candidate indexes; triggering, at a user device, presentation of an index recommendation that identifies a first index of the plurality of candidate indexes, the first index identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving user input indicating a user selection of the first index; and automatically updating a database schema to include the first index in response to receiving the user input.

[0125] In example 2, the subject matter of example 1 includes operations further including detecting that a query execution time for the database query exceeds a threshold, and in response to detecting that the query execution time associated with the database query exceeds the threshold, designating the database query as a slow query and automatically triggering obtaining index recommendations.

[0126] In example 3, the subject matter of example 2 includes operations further including using the index recommendation application to monitor query execution times for each of the plurality of database queries, where the database query is one of a plurality of database queries, and the database query is specified from among the plurality of database queries.

[0127] In Example 4, the subject matter of any of Examples 1 to 3 includes operations further including assessing performance of the first index according to a predetermined performance metric prior to presenting the index recommendation at the user device, and selecting the first index for inclusion in the index recommendation based on the performance of the first index.

[0128] In example 5, the subject matter of example 4 includes assessing the performance of the first index includes determining a first query execution time for the database query when executed on a subset of data from the database associated with the database query without applying the first index, determining a second query execution time for the database query when executed on the subset of data from the database by applying the first index, and comparing the first query execution time to the second query execution time.

[0129] In Example 6, the subject matter of any of Examples 4 to 5 includes causing presentation of the index recommendation at the user device includes causing generation of a user interface that presents the index recommendation together with an indication of performance of the first index.

[0130] In example 7, of any of examples 1 to 6, the subject matter includes each candidate index of the plurality of candidate indexes having one or more candidate index columns, and the statement feature indicating a location of each of the one or more candidate index columns within the database query.

[0131] In example 8, the subject matter of example 7 includes, for each of the one or more candidate index strings, the location is encoded in the feature vector with an encoding scheme that indicates the presence or absence of a respective query term involving the candidate index string.

[0132] In Example 9, the subject matter of any of Examples 1 to 8 includes wherein each candidate index of the plurality of candidate indexes has one or more candidate index columns, and the statistical characteristics include at least one of at least one selectivity value associated with the database query, at least one cardinality value associated with the database query, a number of null values in each of the one or more candidate index columns, or a number of rows associated with each of the one or more candidate index columns.

[0133] In Example 10, the subject matter of any of Examples 1 to 9 includes at least one of the plurality of candidate indexes being a multi-sequence candidate index, and the feature vector for the multi-sequence candidate index being generated by generating sequence-specific values indicative of characteristics of individual sequences in the multi-sequence candidate index, generating multi-sequence values indicative of composite characteristics of the individual sequences in the multi-sequence candidate index, and combining the sequence-specific values and the multi-sequence value.

[0134] In Example 11, of any of Examples 1 to 10, the subject matter includes the plurality of candidate indexes including a first subset and a second subset, the first subset including one or more single-column candidate indexes, and the second subset including one or more multi-column candidate indexes.

[0135] In Example 12, the subject matter of Example 11 includes the one or more machine learning models including a first machine learning model trained to process each feature vector of the one or more single-column candidate indexes, and at least one second machine learning model trained to process each feature vector of the one or more multi-column candidate indexes.

[0136] In Example 13, the subject matter of any of Examples 11 to 12 includes the first index being selected from the first subset, and the index recommendation further identifying a second index from the plurality of candidate indexes, the second index being selected from the second subset and identified in the index recommendation based at least in part on the index recommendation value obtained for the second index.

[0137] In Example 14, the subject matter of any of Examples 1 to 13 includes: each of the one or more machine learning models being trained using a dataset including sample feature vectors, each sample feature vector being labeled to indicate whether a sample index corresponding to the sample feature vector exists in the sample database.

[0138] Example 15 is a method that includes: identifying, by one or more computing devices, a plurality of candidate indexes associated with a database query classified as a slow query; generating, by the one or more computing devices, for each candidate index of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing, by the one or more computing devices, the feature vector to one or more machine learning models to obtain an index recommendation value for each candidate index of the plurality of candidate indexes; causing, by the one or more computing devices, presentation of an index recommendation that identifies a first index of the plurality of candidate indexes, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving, by the one or more computing devices, user input indicating a user selection of the first index; and automatically updating, by the one or more computing devices, a database schema to include the first index in response to receiving the user input.

[0139] In Example 16, the subject matter of Example 15 includes detecting that a query execution time for a database query exceeds a threshold, and in response to detecting that the query execution time associated with the database query exceeds the threshold, designating the database query as a slow query and automatically triggering the acquisition of index recommendations.

[0140] In Example 17, the subject matter of any of Examples 15 to 16 includes, prior to providing the index recommendation, assessing performance of the first index according to a predetermined performance metric; and selecting the first index for inclusion in the index recommendation based on the performance of the first index.

[0141] Example 18 is a non-transitory computer-readable medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations including: identifying a plurality of candidate indexes associated with a database query classified as a slow query; generating, for each of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector for each of the plurality of candidate indexes to a machine learning model to obtain an index recommendation value for the candidate index; causing, at a user device, presentation of an index recommendation that identifies a first index of the plurality of candidate indexes, the first index identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving user input indicating a user selection of the first index; and automatically updating a database schema to include the first index in response to receiving the user input.

[0142] In Example 19, the subject matter of Example 18 includes operations further including detecting that a query execution time for the database query exceeds a threshold, and in response to detecting that the query execution time associated with the database query exceeds the threshold, designating the database query as a slow query and automatically triggering obtaining index recommendations.

[0143] In Example 20, the subject matter of any of Examples 18 to 19 includes operations further including assessing performance of the first index according to a predetermined performance metric prior to presenting the index recommendation at the user device, and selecting the first index for inclusion in the index recommendation based on the performance of the first index.

[0144] Example 21 is at least one machine-readable medium comprising instructions that, when executed by a processing circuitry, cause the processing circuitry to perform operations to implement any of Examples 1-20.

[0145] Example 22 is an apparatus comprising means for implementing any of Examples 1 to 20.

[0146] Example 23 is a system for implementing any of Examples 1 to 20.

[0147] Example 24 is a method for implementing any of Examples 1 to 20.

[0148] 8 is a block diagram illustrating a machine learning program 800 according to some examples. Machine learning programs, also referred to as machine learning algorithms or tools, may be used as part of the systems described herein to perform one or more operations, for example, to identify relationships between reported problems and contextual data, to identify context dependencies, to extract or infer additional problem metadata using initial problem metadata, to generate a priority rating, to generate an estimated impact, or combinations thereof.

[0149] Machine learning is a field of study that gives computers the ability to learn without being explicitly programmed. Machine learning involves the study and construction of algorithms, also referred to herein as tools, that can learn from or be trained with existing data and make predictions about or based on new data. Such machine learning tools operate by assembling models from exemplary training data 808 to make data-driven predictions or decisions, which are represented as outputs or assessments (e.g., assessments 816). Although examples are presented with respect to some machine learning tools, the principles presented herein may be applied to other machine learning tools.

[0150] In some examples, different machine learning tools may be used, for example, logistic regression (LR), naive Bayes, random forest (RF), neural network (NN), matrix factorization, and support vector machine (SVM) tools may be used.

[0151] Two common types of problems in machine learning are classification problems and regression problems. Classification problems, also called categorization problems, aim to classify an item into one of several categorical values (e.g., is this object an apple or an orange?). Regression algorithms aim to quantize some items (e.g., by providing values that are real numbers).

[0152] The machine learning program 800 supports two types of phases: a training phase 802 and a prediction phase 804. In the training phase 802, supervised learning, unsupervised learning, or reinforcement learning may be used. For example, the machine learning program 800 (1) receives features 806 (e.g., as structured or labeled data in supervised learning) and / or (2) identifies features 806 in training data 808 (e.g., unstructured or unlabeled data for unsupervised learning). In the prediction phase 804, the machine learning program 800 uses the features 806 to analyze input data 812 to generate an outcome or prediction, as an example of assessment 816.

[0153] During the training phase 802, feature engineering is used to identify features 806, which may include identifying informative, discriminatory, and independent features for the effective operation of the machine learning program 800 in pattern recognition, classification, and regression. In some examples, the training data 808 includes labeled data, which is known data for pre-identified features 806 and one or more consequences. Each of the features 806 may be a variable or attribute, such as an individual measurable property of a process, article, system, or phenomenon represented by the dataset (e.g., the training data 808). The features 806 may be of different types, such as numeric features, strings, and graphs, and may include, by way of example only, one or more of content 818, concepts 820, attributes 822, historical data 824, and / or user data 826.

[0154] The concept of features in this context relates to that of explanatory variables used in statistical techniques such as linear regression. Selecting informative, discriminative, and independent features is important for the effective operation of machine learning program 800 in pattern recognition, classification, and regression. Features may be of different types, such as numerical features, strings, and graphs.

[0155] In the training phase 802, the machine learning program 800 uses training data 808 to find correlations between features 806 that influence a predicted output outcome or assessment 816. Using the training data 808 and the identified features 806, the machine learning program 800 is trained during the training phase 802 in machine learning program training 810. The machine learning program 800 estimates the values of the features 806 as correlated with the training data 808. The result of the training is a trained machine learning program 814 (e.g., a trained or learned model).

[0156] Additionally, the training phase 802 may involve machine learning, in which the training data 808 is structured (e.g., labeled during a preprocessing operation) and the trained machine learning program 814 implements a relatively simple neural network 828 capable of performing, for example, classification and clustering operations. In other examples, the training phase 802 may involve deep learning, in which the training data 808 is unstructured and the trained machine learning program 814 implements a deep neural network 828 capable of performing both feature extraction and classification / clustering operations.

[0157] The neural network 828 generated during the training phase 802 and implemented within the trained machine learning program 814 may include a hierarchical (e.g., layered) organization of neurons. For example, neurons (or nodes) may be arranged hierarchically in several layers, including an input layer, an output layer, and multiple hidden layers. Each of the layers in the neural network 828 may have one or many neurons, each of which computes by operating a compact function (e.g., an activation function). For example, if the activation function produces a result that exceeds a certain threshold, an output may be communicated from that neuron (e.g., a sending neuron) to a connected neuron (e.g., a receiving neuron) in successive layers. The connections between neurons also have associated weights, which define the influence of the input from the sending neuron to the receiving neuron.

[0158] In some examples, the neural network 828 may be one of several different types of neural networks, including, by way of example only, a single-layer feedforward network, an artificial neural network (ANN), a recurrent neural network (RNN), a symmetrically coupled neural network, and an unsupervised pre-trained network, a transformer network, a convolutional neural network (CNN), or a recurrent neural network (RNN).

[0159] During the prediction phase 804, a trained machine learning program 814 is used to perform the assessment. Input data 812 is provided as input to the trained machine learning program 814, and the trained machine learning program 814 generates an assessment 816 as an output in response to receiving the input data 812.

[0160] FIG. 9 is a block diagram 900 illustrating a software architecture 902 for a computing device, according to some examples. The software architecture 902 may be used with various hardware architectures, for example, as described herein. FIG. 9 is merely a non-limiting illustration of a software architecture, and many other architectures may be implemented to facilitate the functionality described herein. A representative hardware layer 904 is shown, which may represent, for example, any of the computing devices referenced above. In some examples, the hardware layer 904 may be implemented according to the computer system architecture of FIG. 10.

[0161] The exemplary hardware layer 904 comprises one or more processing units 906 having associated executable instructions 908. The executable instructions 908 represent the executable instructions of the software architecture 902, including implementations of the methods, modules, subsystems, components, etc. described herein; the hardware layer 904 may also include memory and / or storage modules 910, which also have the executable instructions 908. The hardware layer 904 may also comprise other hardware, as represented by other hardware 912 and other hardware 922, which represent any other hardware in the hardware layer 904, such as other hardware shown as part of the software architecture 902.

[0162] In the architecture of FIG. 9 , software architecture 902 may be conceptualized as a stack of layers, with each layer providing specific functionality. For example, software architecture 902 may include layers such as operating system 914, libraries 916, framework / middleware layer 918, application 920, and presentation layer 944. In operation, application 920 or other components within a layer may invoke API calls 924 through the software stack and, in response to API calls 924, access responses, return values, etc., shown as messages 926. The layers shown are representative in nature, and not all software architectures have all layers. For example, some mobile or special-purpose operating systems may not provide framework / middleware layer 918, while others may provide such a layer. Other software architectures may include additional or different layers.

[0163] Operating system 914 may manage hardware resources and provide common services. Operating system 914 may include, for example, a kernel 928, services 930, and drivers 932. Kernel 928 may act as an abstraction layer between hardware and other software layers. For example, kernel 928 may be responsible for memory management, processor management (e.g., scheduling), component management, network connectivity, security configuration, etc. Services 930 may provide other common services for other software layers. In some examples, services 930 include an interrupt service. The interrupt service may detect receipt of an interrupt and, in response, cause software architecture 902 to suspend its current processing and execute an interrupt service routine (ISR) when the interrupt is accessed.

[0164] Drivers 932 may be responsible for controlling or interfacing with underlying hardware. For example, drivers 932 may include a display driver, a camera driver, a Bluetooth driver, a flash memory driver, a serial communications driver (e.g., a Universal Serial Bus (USB) driver), a Wi-Fi driver, a near field communication (NFC) driver, an audio driver, a power management driver, etc., depending on the hardware configuration.

[0165] Libraries 916 may provide a common infrastructure that can be used by applications 920 or other components or layers. Libraries 916 typically provide functionality that allows other software modules to perform tasks more easily than by directly interfacing with the functionality of the underlying operating system 914 (e.g., kernel 928, services 930, or drivers 932). Libraries 916 may include system libraries 934 (e.g., the C standard library) that may provide functions such as memory allocation functions, string manipulation functions, mathematical functions, etc. Additionally, libraries 916 may include API libraries 936 such as media libraries (e.g., libraries for supporting the presentation and manipulation of various media formats such as MPEG4, H.264, MP3, AAC, AMR, JPG, PNG, etc.), graphics libraries (e.g., an OpenGL framework that may be used to render two-dimensional and three-dimensional in graphical content on a display), database libraries (e.g., SQLite, which may provide various relational database functions), web libraries (e.g., WebKit, which may provide web browsing functionality), etc. Libraries 916 may also include a wide variety of other libraries 938 for providing many other APIs to applications 920 and other software components / modules.

[0166] The framework / middleware layer 918 may provide a higher-level common infrastructure that can be used by the applications 920 or other software components / modules. For example, the framework / middleware layer 918 may provide various graphic user interface (GUI) functionality, high-level resource management, high-level location services, etc. The framework / middleware layer 918 may provide a wide range of other APIs that can be used by the applications 920 or other software components / modules, although some of the components / modules may be specific to a particular operating system or platform.

[0167] The applications 920 include built-in applications 940 or third-party applications 942. Examples of representative built-in applications 940 may include, but are not limited to, a contacts application, a browser application, a book reader application, a location application, a media application, a messaging application, or a game application. The third-party applications 942 may include any of the built-in applications as well as a wide variety of other applications. In a specific example, the third-party applications 942 (e.g., applications developed using the Android™ or iOS™ Software Development Kit (SDK) by an entity other than the vendor of a particular platform) may be mobile software running on a mobile operating system such as iOS™, Android™, Windows Phone, or other mobile computing device operating system. In this example, the third-party applications 942 may invoke API calls 924 provided by a mobile operating system such as operating system 914 to facilitate the functionality described herein.

[0168] Applications 920 may use built-in operating system facilities (e.g., kernel 928, services 930 or drivers 932), libraries (e.g., system libraries 934, API libraries 936, and other libraries 938), and framework / middleware layer 918 to create a user interface for interacting with a user of the system. Alternatively or additionally, in some systems, interaction with the user may occur through a presentation layer, such as presentation layer 944. In these systems, application / module "logic" can be separated from the aspects of the application / module that interact with the user.

[0169] Some software architectures use a virtual machine. In the example of FIG. 9, this is illustrated by virtual machine 948. The virtual machine creates a software environment in which applications / modules can execute as if they were executing on a hardware computing device. The virtual machine is hosted by a host operating system (operating system 914) and typically, but not always, has a virtual machine monitor 946, which manages the operation of the virtual machine as well as its interface with the host operating system (e.g., operating system 914). The software architecture executes within the virtual machine 948, such as operating system 950, libraries 952, frameworks / middleware 954, applications 956, or presentation layer 958. These layers of the software architecture executing within the virtual machine 948 may be the same as or different from the corresponding layers previously described.

[0170] Some examples are described herein as including logic or several components, modules, or mechanisms. The modules or components may constitute either software modules / components (e.g., code embodied (1) on a non-transitory machine-readable medium or (2) in a transmission signal) or hardware-implemented modules / components. A hardware-implemented module / component is a tangible unit capable of performing several operations, and may be configured or arranged in a particular manner. In examples, one or more computer systems (e.g., standalone, client, or server computer systems) or one or more hardware processors may be configured by software (e.g., applications or application portions) as hardware-implemented modules / components that operate to perform several operations described herein.

[0171] In various examples, a hardware-implemented module / component may be implemented mechanically or electronically. For example, a hardware-implemented module / component may comprise dedicated circuitry or logic that is permanently configured (e.g., as a special-purpose processor such as a field-programmable gate array (FPGA) or an application-specific integrated circuit (ASIC)) to perform certain operations. A hardware-implemented module / component may also comprise programmable logic or circuitry that is temporarily configured by software to perform certain operations (e.g., as contained within a general-purpose processor or another programmable processor). It will be appreciated that the decision to implement a hardware-implemented module / component mechanically, with dedicated and permanently configured circuitry, or with temporarily configured circuitry (e.g., configured by software) may be made based on cost and time considerations.

[0172] Thus, the term "hardware-implemented module" or "hardware-implemented component" should be understood to encompass a tangible entity, an entity that is physically constructed, permanently configured (e.g., hardwired), or temporarily or briefly configured (e.g., programmed) to operate in a particular manner or to perform some operations described herein. Considering examples in which hardware-implemented modules / components are temporarily configured (e.g., programmed), each of the hardware-implemented modules / components need not be configured or instantiated at any one moment. For example, if the hardware-implemented modules / components comprise a general-purpose processor configured using software, the general-purpose processor may be configured as each different hardware-implemented module / component at different times. The software may thus configure the processor, for example, to configure a particular hardware-implemented module / component at one moment and a different hardware-implemented module / component at a different moment.

[0173] Hardware-implemented modules / components can provide information to and receive information from other hardware-implemented modules / components. Accordingly, the described hardware-implemented modules / components may be considered to be communicatively coupled. When various such hardware-implemented modules / components are present simultaneously, communication may be accomplished through signal transmission (e.g., via appropriate circuits and buses connecting the hardware-implemented modules / components). In examples where multiple hardware-implemented modules / components are configured or instantiated at different times, communication between such hardware-implemented modules / components may be accomplished, for example, through the storage and retrieval of information in memory structures to which the multiple hardware-implemented modules / components have access. For example, one hardware-implemented module / component may perform an operation and store the output of that operation in a memory device to which it is communicatively coupled. An additional hardware-implemented module / component may then, at a later time, access the memory device to retrieve and process the stored output. Hardware-implemented modules / components may initiate communication with input or output devices and can operate on resources (e.g., collections of information).

[0174] Various operations of the example methods described herein may be performed, at least in part, by one or more processors that are configured temporarily (e.g., by software) or permanently configured to perform the associated operations. Whether configured temporarily or permanently, such processors may constitute processor-implemented modules / components that operate to perform one or more operations or functions. Modules / components referred to herein may, in some examples, comprise processor-implemented modules / components.

[0175] Similarly, the methods described herein may be at least partially processor-implemented. For example, at least some of the operations of the methods may be performed by one or more processors or processor-implemented modules / components. Performance of some of the operations may be distributed across one or more processors that are not solely within a single machine, but are spread across several machines. In some examples, one processor or multiple processors may be located in a single location (e.g., in a home environment, an office environment, or a server farm), while in other examples, the processors may be distributed across several locations.

[0176] The one or more processors may operate to support performance of the associated operations in a "cloud computing" environment or as "Software as a Service (SaaS)." For example, at least some of the operations may be performed by a group of computers (as examples of machines that include processors), and these operations are accessible over a network (e.g., the Internet) and via one or more suitable interfaces (e.g., APIs).

[0177] Embodiments can be implemented in digital electronic circuitry, or in computer hardware, firmware, or software, or in combinations of them. Embodiments can be implemented by means of a computer program product, e.g., a computer program tangibly embodied in an information carrier, e.g., a machine-readable medium, for execution by, or to control the operation of, a data processing apparatus, e.g., a programmable processor, a computer, or multiple computers.

[0178] A computer program may be written in any form of programming language, including compiled or interpreted languages, and may be deployed in any form, including as a stand-alone program or as a module, subroutine, or other unit suitable for use in a computing environment. A computer program may be deployed to be executed on one computer or on multiple computers at one location, or may be distributed across multiple locations and interconnected by a communications network.

[0179] FIG. 10 is a block diagram of a machine, in the exemplary form of a computer system 1000, upon which instructions 1024 may be executed to cause the machine to perform any one or more of the methodologies discussed herein. In the alternative, the machine may operate as a standalone device or may be connected (e.g., networked) to other machines. In a networked deployment, the machine may operate in the capacity of a server or client machine in a server-client network environment, or as a peer machine in a peer-to-peer (or distributed) network environment. The machine may be a personal computer (PC), a tablet PC, a set-top box (STB), a personal digital assistant (PDA), a cellular phone, a web appliance, a network router, switch, or bridge, or any machine capable of executing instructions (sequential or otherwise) that specify actions to be taken by the machine. Moreover, while only a single machine is shown, the term “machine” shall also include any collection of machines that, individually or jointly, execute a set (or sets) of instructions to perform any one or more of the methodologies discussed herein.

[0180] The exemplary computer system 1000 includes a processor 1002 (e.g., a central processing unit (CPU), a GPU, or both), a primary or main memory 1004, and a static memory 1006, which communicate with each other via a bus 1008. The computer system 1000 may further include a video display unit 1010 (e.g., a liquid crystal display (LCD) or a cathode ray tube (CRT)). The computer system 1000 also includes an alphanumeric input device 1012 (e.g., a keyboard or a touch-sensitive display screen), a UI navigation (or cursor control) device 1014 (e.g., a mouse), a storage unit 1016, a signal generation device 1018 (e.g., a speaker), and a network interface device 1020.

[0181] As used herein, the term “processor” may refer to any one or more circuits or virtual circuits (e.g., physical circuits emulated by logic executing on an actual processor) that manipulate data values in accordance with control signals (e.g., commands, opcodes, machine code, control words, macroinstructions, etc.) and produce corresponding output signals that are applied to operate a machine. A processor may include, for example, at least one of a central processing unit (CPU), a reduced instruction set computing (RISC) processor, a complex instruction set computing (CISC) processor, a graphics processing unit (GPU), a digital signal processor (DSP), a tensor processing unit (TPU), a neural processing unit (NPU), a vision processing unit (VPU), a machine learning accelerator, an artificial intelligence accelerator, an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), a radio frequency integrated circuit (RFIC), a neuromorphic processor, a quantum processor, or any combination thereof. A processor may be a multi-core processor having two or more independent processors (sometimes referred to as “cores”) that can execute instructions simultaneously. A multi-core processor may contain multiple computational cores on a single integrated circuit die, each capable of independently executing program instructions in parallel. Parallel processing on a multi-core processor may be implemented through architectures such as superscalar, VLIW, vector processing, or SIMD, which allow each core to run a separate instruction stream simultaneously. Processors may be emulated as virtual processors or virtual circuits in software running on a physical processor. Virtual processors may behave like independent processors, but are implemented in software rather than hardware.

[0182] The storage unit 1016 includes a machine-readable medium 1022 on which is stored one or more sets of data structures and instructions 1024 (e.g., software) that embody or are used by any one or more of the methodologies or functions described herein. The instructions 1024 may reside, completely or at least partially, within the main memory 1004 or within the processor 1002 during execution by the computer system 1000; the main memory 1004 and the processor 1002 also each constitute a machine-readable medium 1022.

[0183] While the machine-readable medium 1022 is shown, according to some examples, to be a single medium, the term "machine-readable medium" may include a single medium or multiple media (e.g., a centralized or distributed database, or associated caches and servers) that store one or more instructions 1024 or data structures. The term "machine-readable medium" is also intended to include any tangible medium capable of storing, encoding, or carrying instructions 1024 for execution by a machine, causing a machine to perform any one or more of the methodologies of this disclosure, or storing, encoding, or carrying data structures used by or associated with such instructions 1024. The term "machine-readable medium" is therefore intended to include, but is not limited to, solid-state memory, and optical and magnetic media. Examples of machine-readable media 1022 include, by way of example, semiconductor memory devices, such as non-volatile memory including erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), and flash memory devices, magnetic disks such as internal hard disks and removable disks, magneto-optical disks, and compact disc read-only memory (CD-ROM) and digital versatile disc read-only memory (DVD-ROM) disks. Machine-readable media are not transmission media.

[0184] The instructions 1024 may also be transmitted or received over a communications network 1026 using a transmission medium. The instructions 1024 may be transmitted using the network interface device 1020 and any one of several well-known transfer protocols (e.g., Hypertext Transport Protocol (HTTP)). Examples of communications networks include a local area network (LAN), a wide area network (WAN), the Internet, a mobile telephone network, a plain old telephone (POTS) network, and a wireless data network (e.g., Wi-Fi and Wi-Max networks). The term "transmission medium" is intended to include any intangible medium capable of storing, encoding, or carrying instructions 1024 for execution by a machine, including digital or analog communications signals or other intangible media to facilitate communication of such software.

[0185] While specific examples have been described herein, it will be apparent that various modifications and changes may be made to these examples without departing from the broader spirit and scope of the present disclosure. Accordingly, the specification and drawings should be regarded in an illustrative and not a restrictive sense. The accompanying drawings, which form a part of this specification, show, by way of example, and not by way of limitation, specific examples in which the subject matter may be practiced. The examples shown are described in sufficient detail to enable those skilled in the art to practice the teachings disclosed herein. Other examples may be used and derived therefrom, such that structural and logical substitutions and changes may be made without departing from the scope of the present disclosure. This detailed description, therefore, is not to be construed in a limiting sense, and the scope of the various examples is defined solely by the appended claims, along with the full range of equivalents to which such claims are entitled.

[0186] Such examples of the present subject matter may be referred to herein, individually or collectively, by the term "example" merely for convenience and without any intention to intentionally limit the scope of the present application to any single example or concept, even if multiple examples are actually disclosed. Accordingly, while examples are shown and described herein, it should be appreciated that any arrangement calculated to achieve the same purpose may be substituted for the shown example. The present disclosure is intended to cover any adaptations or modifications of the various examples. Combinations of the above examples and other examples not specifically described herein will be apparent to those of skill in the art upon reviewing the above description.

[0187] Portions of the subject matter discussed herein may be presented in terms of algorithms or symbolic representations of operations on data stored as bits or binary digital signals within a machine memory (e.g., a computer memory). Such algorithms or symbolic representations are examples of techniques used by those skilled in the data processing arts to convey the substance of their work to others skilled in the art. As used herein, an "algorithm" is a self-consistent sequence of operations or similar processes leading to a desired result. In this context, an algorithm or operation involves physical manipulations of physical quantities. Typically, though not necessarily, such quantities may take the form of electrical, magnetic, or optical signals capable of being stored, accessed, transferred, combined, compared, or otherwise manipulated by a machine. It is sometimes convenient, principally for reasons of common usage, to refer to such signals in terms such as "data," "content," "bits," "values," "elements," "symbols," "characters," "terms," "digits," "numeric values," or the like. These words are, however, merely convenient labels and should be associated with the appropriate physical quantities.

[0188] Unless otherwise specified, discussions herein using words such as "processing," "calculating," "computing," "determining," "presenting," "displaying," etc. may refer to machine (e.g., computer) actions or processes that manipulate or transform data represented as physical (e.g., electrical, magnetic, or optical) quantities within one or more memories (e.g., volatile memory, non-volatile memory, or any suitable combination thereof), registers, or other machine components that receive, store, transmit, or display information. Furthermore, unless otherwise specified, the terms "a" and "an" are used herein to include one or more instances, as is common in patent documents.

[0189] Unless the context clearly dictates otherwise, throughout the description and claims, words like "comprise," "comprising," and the like, should be construed in an inclusive sense, e.g., "including, but not limited to," as opposed to an exclusive or exhaustive sense. As used herein, the terms "connected," "coupled," or any variations thereof, mean any connection or coupling, either direct or indirect, between two or more elements, and the coupling or connection between the elements may be physical, logical, or a combination thereof. Furthermore, the words "herein," "above," "below," and words of similar import, when used in this application, refer to this application as a whole and not to any particular parts of this application. Where the context permits, words using the singular or plural number may also include the plural or singular number, respectively. Unless otherwise indicated, the word "or" referring to a list of two or more items covers all interpretations of that word: any one of the items in the list, all of the items in the list, and any combination of the items in the list.

[0190] Although some examples, e.g., those shown in the figures, include a particular sequence of operations, the sequence may be modified without departing from the scope of the present disclosure. For example, some of the operations shown may be performed in parallel or in a different sequence without substantially affecting the functionality as described in the examples. In other examples, different components of an example device or system implementing an example method may perform functions at substantially the same time or in a particular sequence. The term "operation" is used to refer to elements in the figures of the present disclosure for ease of reference, and it will be appreciated that each "operation" may identify one or more operations, processes, actions, or steps and may be performed by one or more components. [Explanation of symbols]

[0191] 102 Network 104 Server System 108 User Devices 110 Programmatic Clients 112 Web Client 114 External Server 116 External Server 118 External Applications 120 External Applications 122 Application Server 124 Application Program Interface (API) Server 126 Web Server 128 Database Management Systems 130 Index Recommendation System 132 User Interface System 134 Database Server 136 databases 138 Sample Library 140 Web Interface 142 App Interface 144 Machine Learning Systems 202 Transaction Handling Components 204 Data Modeling Components 206 Storage Management Components 208 Data Integration and Replication Components 210 Security and User Management Components 212 Backup and Restore Components 214 Performance Optimization Components 216 Supervision and Monitoring Tools Components 218 Index Management Components 302 Data Collection Components 304 Feature Extraction Components 306 Vector Generation Components 308 Model Interface Components 310 Recommended Analysis Components 312 User Interaction Components 314 Index Integration Components 316 Performance Monitoring Components 800 Machine Learning Programs 814 trained machine learning programs 828 Neural Networks, Deep Neural Networks 902 Software Architecture 904 Hardware Layer 906 Processing Unit 912 Other Hardware 914 Operating Systems 916 Library 918 Framework / Middleware Layer 920 Applications 922 Other Hardware 928 Kernel 930 Service 932 Driver 934 System Library 936 API Library 938 other libraries 940 Built-in Applications 942 Third-Party Applications 944 Presentation Layer 946 Virtual Machine Monitor 948 Virtual Machine 958 Presentation Layer 1000 Computer Systems 1002 processor 1004 Primary or Main Memory 1006 Static Memory 1008 Bus 1010 Video Display Unit 1012 Alphanumeric Input Device 1014 UI navigation device 1016 Storage Unit 1018 Signal Generating Device 1020 Network Interface Device 1022 Machine-readable medium 1026 Communication Network

Claims

1. at least one memory for storing instructions; one or more processors configured by the instructions to perform the operations; and the operation comprises: identifying a plurality of candidate indexes associated with database queries classified as slow queries; generating, for each candidate index of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector to one or more machine learning models to obtain an index recommendation for each candidate index of the plurality of candidate indexes; triggering, at the user device, presentation of an index recommendation identifying a first index of the plurality of candidate indexes, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving a user input indicating a user selection of the first index; automatically updating a database schema to include the first index in response to receiving the user input; Including, the system.

2. The operation is detecting that a query execution time of the database query exceeds a threshold; in response to detecting that the query execution time associated with the database query exceeds the threshold; designating the database query as a slow query; and automatically triggering said obtaining of said index recommendation value; The system of claim 1 further comprising:

3. The database query is one of a plurality of database queries, and the operation comprises:

3. The system of claim 2, further comprising using an index recommendation application to monitor the query execution time for each of the plurality of database queries, the database query being specified from among the plurality of database queries.

4. The operation, prior to the presentation of the index recommendation at the user device, includes: assessing the performance of the first index according to a predetermined performance metric; selecting the first index for inclusion in the index recommendation based on the performance of the first index; The system of claim 1 further comprising:

5. The assessment of the performance of the first index comprises: determining a first query execution time for the database query when executed on a subset of data from a database associated with the database query without applying the first index; determining a second query execution time of the database query when executed against the subset of data from the database by applying the first index; comparing the first query execution time with the second query execution time; The system of claim 4, comprising:

6. 5. The system of claim 4, wherein causing the presentation of the index recommendation at the user device includes causing generation of a user interface that presents the index recommendation along with an indication of the performance of the first index.

7. 2. The system of claim 1, wherein each candidate index of the plurality of candidate indexes has one or more candidate index columns, and the statement features indicate a location of each of the one or more candidate index columns within the database query.

8. 8. The system of claim 7, wherein for each of the one or more candidate index strings, the location is encoded in the feature vector with an encoding scheme that indicates a presence or absence of a respective query term involving the candidate index string.

9. 2. The system of claim 1, wherein each candidate index of the plurality of candidate indexes has one or more candidate index columns, and the statistical characteristics include at least one of at least one selectivity value associated with the database query, at least one cardinality value associated with the database query, a number of null values in each of the one or more candidate index columns, or a number of rows associated with each of the one or more candidate index columns.

10. At least one of the plurality of candidate indexes is a multi-sequence candidate index, and the feature vector for the multi-sequence candidate index comprises: generating column-specific values that characterize individual columns in the multi-column candidate index; generating a multi-column value indicative of a composite characteristic of the individual columns in the multi-column candidate index; combining the column specific values and the multi-column values; The system of claim 1 , wherein the system is generated by:

11. 2. The system of claim 1, wherein the plurality of candidate indexes comprises a first subset and a second subset, the first subset comprising one or more single-column candidate indexes and the second subset comprising one or more multi-column candidate indexes.

12. 12. The system of claim 11, wherein the one or more machine learning models include a first machine learning model trained to process each feature vector of the one or more single-column candidate indexes and at least one second machine learning model trained to process each feature vector of the one or more multi-column candidate indexes.

13. 12. The system of claim 11, wherein the first index is selected from the first subset, and the index recommendation further identifies a second index from the plurality of candidate indexes, the second index being selected from the second subset and identified in the index recommendation based at least in part on the index recommendation value obtained for the second index.

14. 2. The system of claim 1, wherein each of the one or more machine learning models is trained using a dataset including sample feature vectors, each sample feature vector being labeled to indicate whether a sample index corresponding to the sample feature vector exists in a sample database.

15. identifying, by one or more computing devices, a plurality of candidate indexes associated with database queries classified as slow queries; generating, by the one or more computing devices, and for each candidate index of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing, by the one or more computing devices, the feature vector to one or more machine learning models to obtain an index recommendation value for each candidate index of the plurality of candidate indexes; causing presentation, by the one or more computing devices, of an index recommendation identifying a first index of the plurality of candidate indexes, the first index identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving, by the one or more computing devices, user input indicating a user selection of the first index; automatically updating, by the one or more computing devices, a database schema to include the first index in response to receiving the user input; A method comprising:

16. detecting, by the one or more computing devices, that a query execution time for the database query exceeds a threshold; in response to detecting that the query execution time associated with the database query exceeds the threshold; designating, by the one or more computing devices, the database query as a slow query; and automatically triggering, by the one or more computing devices, the obtaining of the index recommendation values; 16. The method of claim 15, further comprising:

17. Prior to providing such index recommendation, assessing, by the one or more computing devices, the performance of the first index according to a predetermined performance metric; selecting, by the one or more computing devices and based on the performance of the first index, the first index for inclusion in the index recommendation; 16. The method of claim 15, further comprising:

18. A non-transitory computer-readable medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform operations, including: identifying a plurality of candidate indexes associated with database queries classified as slow queries; generating, for each candidate index of the plurality of candidate indexes, a feature vector representing statement features and statistical features associated with the candidate index; providing the feature vector for each candidate index of the plurality of candidate indexes to a machine learning model to obtain an index recommendation for the candidate index; triggering, at the user device, presentation of an index recommendation identifying a first index of the plurality of candidate indexes, the first index being identified in the index recommendation based at least in part on the index recommendation value obtained for the first index; receiving a user input indicating a user selection of the first index; automatically updating a database schema to include the first index in response to receiving the user input; 1. A non-transitory computer-readable medium comprising:

19. The operation is detecting that a query execution time of the database query exceeds a threshold; in response to detecting that the query execution time associated with the database query exceeds the threshold; designating the database query as a slow query; and automatically triggering said obtaining of said index recommendation value; 20. The non-transitory computer-readable medium of claim 18, further comprising:

20. The operation, prior to presenting the index recommendation at the user device, includes: assessing the performance of the first index according to a predetermined performance metric; selecting the first index for inclusion in the index recommendation based on the performance of the first index; 20. The non-transitory computer-readable medium of claim 18, further comprising:

Images