Unexpected Behavior: Index on Column Not Returning Results

ScyllaDB
1

Hello everyone,

I’ve recently encountered a peculiar issue and was hoping to get some insights or solutions from this community.

Background: I have a database table with several columns. For optimization purposes, I have created separate secondary indexes on a few of these columns, and they work perfectly fine. However, I’ve run into a problem with the latest column I indexed.

The Issue: After creating an index on a specific column (let’s call it ColumnX), when I perform a search based on that column, it returns no results. The strange part is, I am certain that the data I’m searching for exists in the table because when I search for the same data in a different field (say ColumnY), I can locate the desired entry.

Steps Taken So Far:

  1. Verified that the index was created successfully without any errors.
  2. Ensured that the data I’m searching for in ColumnX does exist in the table.
  3. Checked the query to ensure that there aren’t any typos or logical errors.
  4. Reindexed the column to see if it resolves the issue (it didn’t).

Additional Information:

  • Version: 3.0.8
  • Index Query: CREATE INDEX idx_db1_cl1 ON main.db1 (cl1);
  • Query Used: SELECT * FROM main.db1 WHERE cl1 = '0123';

I’m baffled as to why this is happening, especially since I’ve indexed other columns in the same manner without any issues. Has anyone else encountered this problem or have any suggestions on how to fix it? Any help or insights would be greatly appreciated.

Thank you in advance!

2

This release is ancient, use a supported version >= 5.1 and retry after you
run repair of the base table

3

thanks but issued solve without doing anything i think because of adding data while creating index and i don’t know how to track index creation because i was able to see index thought that’s an issue.

I've created a secondary index during the addition of data, but when I try to search using that index, it doesn't seem to work properly. What's more, I've noticed that after adding the dataset, the CPU usage remains at 40% in an idle state. This has raised concerns that there might be some background process related to index creation consuming the CPU. I have ScyllaDB running on a dedicated server. How can I check what's taking up that 40% CPU, and how can I ensure that the secondary index has been created and is functioning correctly? I've already checked the system_schema.indexes table, and I can see the index listed there.