How to use the Index Advisor to recommend indexes for your queries.
Introduction
You don’t need to create an index to query a keyspace. However, you can improve the performance of your query by using a well-designed index. In Couchbase Server Enterprise Edition, the Index Advisor can analyze your queries and provide recommended indexes to optimize response times.
If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:
Advice for a Single Query
To get index recommendations for a single query, you can use the Index Advisor in the Query Workbench, the ADVISE statement, or the ADVISOR() function.
-
Query Workbench
-
ADVISE Statement
-
ADVISOR() Function
To get index recommendations for a single query, do one of the following:
-
Enter the query in the Query Editor and click Index Advisor.
-
Alternatively, if the query has already been executed, click Advice in the Results area.
The Results area displays the details of any current indexes used by the query, and any indexes that the Index Advisor recommends.
If there are any recommended indexes, you can click Create & Build Indexes to create them.
Similarly, if there are any recommended covering indexes, you can click Create & Build Covering Indexes to create them.
For more details, refer to Index Advisor.
To get index recommendations for a single query, use the ADVISE statement, followed by the query for which you want advice.
The ADVISE statement returns a JSON object containing the details of any current indexes used by the query, and any indexes that the Index Advisor recommends, along with the reasons for recommendation.
For each index, a SQL++ statement is provided: you can copy and run this index statement to create the recommended index.
The following example gets index advice for a single query.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
ADVISE SELECT airportname FROM airport
WHERE geo.alt NOT BETWEEN 0 AND 100;[
{
"#operator": "Advise",
"advice": {
"#operator": "IndexAdvice",
"adviseinfo": {
"current_indexes": [
{
"index_statement": "CREATE PRIMARY INDEX idx_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
"keyspace_alias": "airport"
}
],
"recommended_indexes": {
"covering_indexes": [
{
"index_statement": "CREATE INDEX adv_geo_alt_airportname ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`,`airportname`)",
"keyspace_alias": "airport"
}
],
"indexes": [
{
"index_statement": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)",
"keyspace_alias": "airport",
"recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (5. less than/greater than)."
}
]
}
}
},
"query": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]For more details, refer to ADVISE.
To get index recommendations for a single query, use the ADVISOR() function with a string argument, representing the query.
The ADVISOR() function returns a JSON object containing the details of any current indexes used by each query, and any indexes that the Index Advisor recommends.
For each index, a SQL++ statement is provided: you can copy and run this index statement to create the recommended index.
The following example gets index advice for a single query.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT ADVISOR("SELECT * FROM landmark
WHERE activity = 'eat' AND city = 'Paris'") AS Single;[
{
"Single": {
"current_used_indexes": [
{
"index": "CREATE INDEX def_inventory_landmark_city ON `default`:`travel-sample`.`inventory`.`landmark`(`city`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT * FROM `travel-sample`.inventory.landmark\nWHERE activity = 'eat' AND city = 'Paris'"
}
]
}
],
"recommended_indexes": [
{
"index": "CREATE INDEX adv_city_activity ON `default`:`travel-sample`.`inventory`.`landmark`(`city`,`activity`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT * FROM `travel-sample`.inventory.landmark\nWHERE activity = 'eat' AND city = 'Paris'"
}
]
}
]
}
}
]For more details, refer to ADVISOR(string).
Advice for Multiple Queries
The ADVISOR() function also enables you to get index recommendations for multiple queries.
To get advice for multiple queries, use the ADVISOR() function with an array argument containing strings which represent each query.
| You can query the system:completed_requests catalog to get a list of recently-completed queries. |
The following example gets index advice for recently-completed queries.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT ADVISOR((SELECT RAW statement FROM system:completed_requests)) AS Recent;[
{
"Recent": {
"current_used_indexes": [
{
"index": "CREATE PRIMARY INDEX def_inventory_route_primary ON `default`:`travel-sample`.`inventory`.`route`",
"statements": [
{
"run_count": 3,
"statement": "SELECT * FROM `travel-sample`.inventory.route r JOIN `travel-sample`.inventory.airline a ON r.airlineid= META(a).id WHERE a.country = \"France\";"
},
{
"run_count": 2,
"statement": "SELECT d.id, d.destinationairport, RATIO_TO_REPORT(d.distance) OVER (PARTITION BY d.destinationairport) AS `distance-ratio` FROM `travel-sample`.inventory.route AS d LIMIT 7;"
},
// ...
]
},
{
"index": "CREATE PRIMARY INDEX def_inventory_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
"statements": [
{
"run_count": 1,
"statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]
}
],
"recommended_covering_indexes": [
{
"index": "CREATE INDEX adv_geo_alt_airportname ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`,`airportname`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]
}
],
"recommended_indexes": [
{
"index": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]
},
{
"index": "CREATE INDEX adv_airlineid ON `default`:`travel-sample`.`inventory`.`route`(`airlineid`)",
"statements": [
{
"run_count": 3,
"statement": "SELECT * FROM `travel-sample`.inventory.route r JOIN `travel-sample`.inventory.airline a ON r.airlineid= META(a).id WHERE a.country = \"France\";"
}
// ...For more details, refer to ADVISOR(array).
Advice for a Session
The ADVISOR() function also enables you to get index recommendations for all the queries that you run in an Index Advisor session.
To run an Index Advisor session:
-
Use the
ADVISOR()function with a start object argument to start the session. The object argument must contain the property"action": "start", and must also contain a"duration"property, specifying the duration of the session.The query returns a session ID, which you must use to get the results for this session, and to perform other actions on this session.
-
Run all the queries for which you require index recommendations.
-
If you want to stop the session early, use the
ADVISOR()function with a stop object argument. The object argument must contain the property"action": "stop", and must also contain a"session"property, specifying the session ID. -
When the session is complete, use the
ADVISOR()function with a get object argument to get the index recommendations. The object argument must contain the property"action": "get", and must also contain a"session"property, specifying the session ID.
You can also use the ADVISOR() function to abandon a session without recording any results; to list active and completed sessions; and to purge the results of an Index Advisor session.
|
The following example starts an Index Advisor session with a duration of 1 hour. All queries taking longer than 0 seconds will be collected.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT ADVISOR({"action": "start", "response": "0s", "duration": "1h"}) AS Collect;[
{
"Collect": {
"session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"
}
}
]The following example stops the Index Advisor session early and saves the results.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT ADVISOR({"action": "stop", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"})
AS Stop;[
{
"Stop": []
}
]The following example returns index recommendations for the queries in the Index Advisor session.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
SELECT ADVISOR({"action": "get", "session": "0cd09ae4-a083-4a7e-86cd-85e42c140d60"})
AS Get;[
{
"Get": [
[
{
"current_used_indexes": [
{
"index": "CREATE PRIMARY INDEX idx_airport_primary ON `default`:`travel-sample`.`inventory`.`airport`",
"statements": [
{
"run_count": 1,
"statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]
// ...
}
],
"recommended_covering_indexes": [
{
"index": "CREATE INDEX adv_city_name ON `default`:`travel-sample`.`inventory`.`hotel`(`city`,`name`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT h.name, h.city, a.airportname FROM `travel-sample`.inventory.hotel h JOIN `travel-sample`.inventory.airport a ON h.city = a.city LIMIT 5;"
}
]
// ...
}
],
"recommended_indexes": [
{
"index": "CREATE INDEX adv_geo_alt ON `default`:`travel-sample`.`inventory`.`airport`(`geo`.`alt`)",
"statements": [
{
"run_count": 1,
"statement": "SELECT airportname FROM `travel-sample`.inventory.airport WHERE geo.alt NOT BETWEEN 0 AND 100;"
}
]
}
]
}
]
]
}
]For more details, refer to ADVISOR(start_obj).