Database Reference
In-Depth Information
SELECT * FROM searchable_tab WHERE name = 'Light Yagami';
code=2200 [Invalid query] message="No indexed columns
present in by-columns clause with Equal operator"
So, we create index on that to make this search work:
CREATE INDEX searchable_tab_name_idx ON searchable_tab
(name);
SELECT * FROM searchable_tab WHERE name = 'Light Yagami';
id | age | name | phones
----+-----+--------------+------------------
2 | 26 | Light Yagami | {'111-277-0563'}
(1 rows)
Indexes are of great help in Cassandra. So why not just go ahead and create indexes for all
the columns? There are a couple of reasons to that. First, indexes are not free. Indexes are
maintained in separate columns' families, and extra work is needed to be done every time
one mutates a column that has an index on it. The second thing is cardinality, which is true
to the database indexes in general. The way Cassandra stores indexes is by creating a re-
verse index in a separate table; let's call it an index table. Let's also define the table on
which the index is created as a parent table. The index table can be assumed as a table
with very large rows where each cell can have any arbitrary name. (Traditionally, this is a
wide row column family.) The index table contains primary keys of the parent table as the
cell names and the values of the column of the parent table as primary keys. Let's see an
example of a table with the primary key as
user_id
and an indexed column,
city
:
user_id | city
-----------+---------------------------
1 | Cape Town
2 | Kabul
3 | Mogadishu
4 | Cape Town
5 | Baghdad
6 | Kabul
