This is our Feb drop for Index Advisor service after fixing some of the bugs that were found. We plan to keep improvising the Index Advisor service(What is it?). The fixes in Index Advisor that you would normally see in the next maintenance release will now be available to you every month.
Who is it for? and When should you use it?
http://index-advisor.couchbase.com/
At the cost of repeating myself,
This service will provide index recommendations to help DBAs, developers, and architects optimize query performance and meet the SLAs.
You will find this service useful if you:
- Want to avoid reading the index creation rules, understand them, and implement them to find the appropriate indexes for your query/queries/workload.
- Â Do not want to download the latest Couchbase 6.5 server yet.
-  Are using an older Couchbase version(version 5.5,6.0) and need help creating the right indexes for your queries.
- Want to generate advice for indexes without creating a bucket or uploading the schema or data.
Whats new?
Even if you have the Couchbase Server 6.5 server downloaded and can use Index Advisor from Query Workbench, this index Advisor is a more recent version(without a great looking UI) but with the following defects fixed.
1. Index advise on virtual keyspace
2. Index Advisor to support virtual keyspace for delete/merge/update statement
With both these fixes, a query like
|
1 |
advise delete FROM aa where type="beer" ; |
that used to give
|
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #ff0000">[ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": [] }, "query": "delete FROM aa where type=\"beer\" ;" } ]</span> |
now gives
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_type ON `aa`(`type`)", "keyspace_alias": "aa" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_type ON `aa`(`type`)", "keyspace_alias": "aa", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing." } ] } } }, "query": "delete FROM aa where type=\"beer\" ;" } ] } |
3. Index Advisor to remove identifier in Meta() expression
For a query like:
|
1 |
SELECT * FROM `beer-sample` d where SPLIT(meta(d).id, ":")[0] like "%cafe" |
we would get
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<span style="color: #ff0000">{ "adviseResult": [ { "current_indexes": [ { "index_statement": "CREATE PRIMARY INDEX beer_primary ON `beer-sample`", "keyspace_alias": "beer-sample_d" } ], "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_split_meta_d_id_0 ON `beer-sample`(split((meta(`d`).`id`), ':')[0])", "keyspace_alias": "beer-sample_d", "recommending_rule": "Index keys follow order of predicate types: 9. function index." } ] } } ] }</span> |
Now we have modified that to:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "indexes": [ { "index_statement": "CREATE INDEX adv_split_meta_self_id_0 ON `beer-sample`(split((<span style="color: #3366ff">meta(`self`)</span>.`id`), ':')[0])", "keyspace_alias": "beer-sample_d", "recommending_rule": "Index keys follow order of predicate types: 9. like." } ] } } }, "query": "SELECT * FROM `beer-sample` d where SPLIT(meta(d).id, \":\")[0] like \"%cafe\"" } ] } |
for better usage.
4. Query: Advise function confusing message
A bunch of confusing error messages have been replaced by more verbose and clear explanations.
5. Using advise on a query that has USE index keyword returns incorrect error
|
1 |
advise select id,url,address,title,city,name,country,public_likes from bucket0 <strong>use index (def_primary)</strong> where some p in public_likes satisfies p like "Ge%" END AND type = "hotel" order by id, url, address, title, city DESC, name, country |
used to give
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<span style="color: #ff0000">[ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": [ { "recommended_indexes": "No index recommendation at this time: no keyspace found." } ] }, "query": "select id,url,address,title,city,name,country,public_likes from bucket0 use index (def_primary) where some p in public_likes satisfies p like \"Ge%\" END AND type = \"hotel\" order by id, url, address, title, city DESC, name, country" } ]</span> |
now gives
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "results": [ { "#operator": "Advise", "advice": { "#operator": "IndexAdvice", "adviseinfo": { "recommended_indexes": { "covering_indexes": [ { "index_statement": "CREATE INDEX adv_type_DISTINCT_public_likes_id_url_city_name_title_address_country_public_likes ON `bucket0`(`type`,DISTINCT ARRAY `p` FOR p in `public_likes` END,`id`,`url`,`city`,`name`,`title`,`address`,`country`,`public_likes`)", "keyspace_alias": "bucket0" } ], "indexes": [ { "index_statement": "CREATE INDEX adv_type_DISTINCT_public_likes ON `bucket0`(`type`,DISTINCT ARRAY `p` FOR p in `public_likes` END)", "keyspace_alias": "bucket0", "recommending_rule": "Index keys follow order of predicate types: 2. equality/null/missing, 6. array predicate." } ] } } }, "query": "select id,url,address,title,city,name,country,public_likes from bucket0 use index (def_primary) where some p in public_likes satisfies p like \"Ge%\" END AND type = \"hotel\" order by id, url, address, title, city DESC, name, country" } ] } |
Some more smaller enhancements have been added as well.
Give it a try and any problems you see with Indexes recommended by Index Advisor Service, you can add them as a comment to this blog
More about the Index Advisor feature:
https://www.couchbase.com/blog/index-advisor-service/
https://www.couchbase.com/blog/n1ql-index-advisor-improve-query-performance-and-productivity/
https://www.couchbase.com/blog/index-advisor-for-query-workload/
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/advise.html
https://docs.couchbase.com/server/6.5/n1ql/n1ql-language-reference/advisor.html
https://docs.couchbase.com/server/6.5/tools/query-workbench.html#index-advisor
