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.
“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!