Missing results when using WHERE, LIMIT and OFFSET without an ORDER BY

SQL++
1

Hello everyone,

We’re using Couchbase (v6.6.0 for this test) for a project at work and to optimize slow queries we’re trying to use indexes. We used the advisor to build which index to use but when we implement it, we begin having some weird results in other queries.

In a bucket, we have documents of different types, the interesting ones here are work sites and time sheets. We aggregate time sheets over several weeks to compute if alerts should be triggered and the query doing this was quite slow so we decided to use an index. The index does optimize our query, the HTTP request building our time sheets gained 20 seconds so we’re quite happy about this but in an other page, we have a Select2 of work sites with a paginated search which begins malfunctioning as soon as the index is implemented.

Here is the query,

SELECT
  w.`id`, w.`code`, w.`nom`
FROM
  `bucket` w
WHERE
  w.`type` = 'chantier' AND (LOWER(w.`code`) LIKE '%paris d%' OR LOWER(w.`nom`) LIKE '%paris d%')
LIMIT 
  20
OFFSET
  0

This query should give us 2 results but we have none, even if the COUNT one does give us 2 as an output,

SELECT
  COUNT(*) AS `total`
FROM
  `bucket`
WHERE
  `type` = 'chantier' AND (LOWER(`code`) LIKE '%paris d%' OR LOWER(`nom`) LIKE '%paris d%')

I tried removing LIMIT or increasing it and we get our results. I then tried using an ORDER BY after Googling and it did get us our results too. Using EXPLAIN I found out that IntersectScan seems to apply a LIMIT when not using ORDER BY.

IntersectScan
IntersectScan1726×576 51.9 KB

“Avec ORDER BY” means “With ORDER BY” and "Sans “ORDER BY” means “Without ORDER BY”.

It might be the expected behavior but I haven’t been able to find out if it indeed is, so, if anyone knows, could you please enlighten us?

Have a good day!

2

Avoid InterSectScan. If required use v6.6.1

You are doing pattern matching with leading wild card. There is no easy way scan index with leading wild card. You can use FTS Index (Full Text Search Category Archives | The Couchbase Blog)

Another option is A Couchbase Index Technique for LIKE Predicates With Wildcard - DZone Database

If required provide hint USE INDEX(ix1)

CREATE INDEX ix1 ON bucket( DISTINCT ARRAY LOWER(v) FOR v IN [code, nom] END) WHERE type = "chantier";
SELECT w.`id`, w.`code`, w.`nom`
FROM `bucket` AS w
WHERE w.`type` = 'chantier' AND ANY v IN [w.code, w.nom] SATISFIES LOWER(v) LIKE "%paris d%" END
LIMIT 20
OFFSET 0;
3

Hello vsr1,

Thanks for your quick response, sorry I couldn’t get back to you sooner.

I tried as you suggested USE INDEX and I did get my results as expected, I’ll see with my boss, who’s in contact with our client, if I should go through every request and add USE INDEX when necessary.

Have a good day!