0

I am using SQLite3 in C++ where I need to store text data. There are 7 columns in the table. I need to filter data by 7 columns. I need to check for equality of 6 columns to a specific value, and I need to search for a substring by the last column. I use a regular index for the first 6 columns and the search (SELECT query) works quite fast. However, for the 7th column, I use LIKE %value% to search for a substring. However, LIKE does not use indexes. How can I efficiently implement searching by this field? Note that WHERE filtering by fields can contain all 7 columns, or just one (which uses LIKE).

UPDATE I found out that full text search works well in such cases, but full text search does not work with a regular table, as far as I know. What solution do you recommend?

My texts are not long (up to 100 characters) and there are no spaces. Example: ThisIsMyExampleString. In that case I'm not sure that full text search will work here

Improve this question
21
  • 1
    Back a zillion years ago when I was working with relational databases, we'd split out our string data to separate tables. For example a customer's name would be a first name id, and a last name id; those would look up into a first name table and a last name table. If we needed to find all customers who had Joe or Joseph as their first name, it'd be a lot faster to find the two first name id's from the first name table, and then find all records in the customer table that had the first name id that matched either of those ids.
    – Eljay
    Commented Jul 21 at 12:23
  • @Eljay Sorry, but how does this story answer my question?
    – Joe J
    Commented Jul 21 at 12:28
  • 1
    Have you tried INSTR,see how that performs?
    – Nathan_Sav
    Commented Jul 21 at 13:25
  • 2
    Select string where instr(sting, "whatImSearchingFor")>0 will return the entire string if "whatImSearchingFor" is contained within it. The point is that LIKE needs to be more complicated than Instr() to handle wildcards anywhere so instr() may perform better. You have no hope of using an index lookup on a substring search since all possible substrings would need to be indexed.
    – Chris Maurer
    Commented Jul 21 at 13:39
  • 3
    Then, other than making a mapping table of every possible substring in your data, there is no logical way to optimise your search. No constraints == no options.
    – MatBailie
    Commented Jul 22 at 9:45
Related questions
3410
Improve INSERT-per-second performance of SQLite
3322
How to add a column with a default value to an existing table in SQL Server?
1921
Image Processing: Algorithm Improvement for 'Coca-Cola Can' Recognition
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.