# ANN_SEARCH + WHERE clause causing full table scan in OceanBase 4.

> Source: <https://promptcube3.com/en/threads/4453/>
> Published: 2026-07-31 00:07:14+00:00

# ANN_SEARCH + WHERE clause causing full table scan in OceanBase 4.

We are building a multi-tenant enterprise

[RAG](/en/tags/rag/)system and just hit a gnarly performance issue with OceanBase 4.3.x (MySQL mode). The setup is pretty straightforward: a large table that holds 1536-dimensional embeddings (FLOAT array) with a native VSAG vector index, plus a`tenant_id`

integer column with a standard B-tree index and a `status`

column. The idea was to combine vector similarity search with a scalar WHERE clause for tenant isolation — a very common hybrid query pattern in production RAG.Here’s the exact query we’re stuck with:

```
SELECT doc_id, chunk_text
FROM enterprise_knowledge_base
WHERE tenant_id = 10025 AND status = 'ACTIVE'
ORDER BY APPROX_DISTANCE(embedding_data, [0.015, -0.023, 0.112, 0.045])
APPROX_TOP 5;
```

We expected OceanBase to pre‑filter on `tenant_id`

and `status`

first, then run the ANN search only on the matching rows. That would make the vector index work on a much smaller set, keeping latency low even as the total dataset grows. But when we tested it against a realistic dataset (a few million rows across

[Next My AI Agent Task Organization System →](/en/threads/4441/)

## All Replies （4）

A

Does the vector index get used after the query is restructured?

0

R

That's a good question — I've seen cases where restructuring helps, but OceanBase still struggles with hybrid queries.

0

D

We hit the same problem; splitting the WHERE into a subquery fixed it for us.

0

R

We experienced this as well; rewriting the query with a derived table fixed it.

0