[4.5.1] How to create covering index for range value and order by?

Couchbase Server
1

Hi,

For example, if the query like this:
SELECT id FROM default WHERE price = 50 ORDER BY price, timeModified
We can create on (price, timeModified) so that CB no need to do ordering (alot faster)

So, I have a query like this :
SELECT id FROM default WHERE price > 10 and price < 100 ORDER BY timeModified ASC

Are there anyway to create covering index that cover “timeModified” order also ?

2

Yes.

CREATE INDEX idx_price ON default(timeModified, price, id)
WHERE price > 10 AND price < 100;

EXPLAIN SELECT id
FROM default
WHERE price > 10 AND price < 100 AND timeModified IS NOT NULL
ORDER BY timeModified ASC;
3

For example, there are 1million records and all dont’ have price from 10 to 100, then the query will run very slow…

4

Did you create the exact above.

5

Hi Gerald,
This is my index:

CREATE INDEX gia_idx ON default(ngayDangTin,gia,id)

This is my query:

SELECT id FROM default use index (gia_idx)
WHERE gia > 99 AND gia < 100 AND ngayDangTin IS NOT NULL
ORDER BY ngayDangTin ASC

It took me “Execution: 6.53s Result Count: 2”, my bucket have 667,853 records

This is the explain:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan",
              "covers": [
                "cover ((`default`.`ngayDangTin`))",
                "cover ((`default`.`gia`))",
                "cover ((`default`.`id`))",
                "cover ((meta(`default`).`id`))"
              ],
              "index": "gia_idx",
              "index_id": "ce76fbf827f28467",
              "keyspace": "default",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 0,
                    "Low": [
                      "null",
                      "99"
                    ]
                  }

If I create index on “gia” first, then the result alot better:
Index:

CREATE INDEX gia2_idx ON default(gia,ngayDangTin,id)

Query:

SELECT id FROM default use index (gia2_idx)
WHERE gia > 99 AND gia < 100 AND ngayDangTin IS NOT NULL
ORDER BY ngayDangTin ASC

Query result:

Execution: 7.20ms Result Count: 2

Thank you

1 Like