# Indexes for columns

**URL:** <https://community.cratedb.com/t/indexes-for-columns/1652>\
**Category:** CrateDB\
**Created:** [November 22, 2023, 2:33pm UTC](https://community.cratedb.com/t/indexes-for-columns/1652 "2023-11-22T14:33:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [November 22, 2023, 2:33pm UTC](https://community.cratedb.com/t/indexes-for-columns/1652/1 "2023-11-22T14:33:26Z")

</div>

Hi all,

I understand that with a column with INDEX OFF that it is a result set only, but surely if you have a query that uses indexed columns AND a column that is not indexed, it would use the indexes first and then scan the result set to apply the non indexed query parameter ?

I’ve tried this but it does not seem to work, even with a CTE. It just complains about the non indexed column.

Many thanks  
David.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [November 22, 2023, 4:18pm UTC](https://community.cratedb.com/t/indexes-for-columns/1652/2 "2023-11-22T16:18:39Z")

</div>

I assume you are referring to something like this?

```sql
CREATE TABLE a (a TEXT INDEX OFF, b TEXT);

```

```sql
SELECT * FROM a
WHERE a = 'Hello' AND b = 'World';
--SQLParseException[Cannot search on field [a] since it is not indexed.]

```

* * *

a workaround might be something like this:

```sql
SELECT * FROM a
WHERE ''||a = 'Hello' AND b = 'World';

```

or

```sql
SELECT * FROM a
WHERE a||null = 'Hello' AND b = 'World';

```

but I also need to check if there might be a better option

---

<div class="post-metadata">

**Author:** ![smu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/smu/32/60_2.png) [@smu](https://community.cratedb.com/u/smu)\
**Post date:** [November 23, 2023, 11:31am UTC](https://community.cratedb.com/t/indexes-for-columns/1652/3 "2023-11-23T11:31:28Z")

</div>

Not helpful now, but still worth mentioning: we’ve fixed that behaviour with [Allow columns without doc-values or unindexed to be queryable by jeeminso · Pull Request #14856 · crate/crate · GitHub](https://github.com/crate/crate/pull/14856) and such allow queries against non-indexed columns in future releases (or using the current nightly) in the way you described it (first use indices then apply a generic filter on the result set).

---

<div class="post-metadata">

**Author:** ![djbestenergy](https://avatars.discourse-cdn.com/v4/letter/d/8c91f0/32.png) [@djbestenergy](https://community.cratedb.com/u/djbestenergy)\
**Post date:** [November 23, 2023, 11:42am UTC](https://community.cratedb.com/t/indexes-for-columns/1652/4 "2023-11-23T11:42:31Z")

</div>

Thank you, this is great.

In my case I’m just querying on double precision fields, so I used  
`AND 0||f = 49.9423`

as a similar method for numeric fields.