Querying data not in database / index

Databases
1

This is not Xojo specific question but wanted to get peoples opinion. I have a situation whereby I need to query a database for data that doesn’t exist in certain rows within the table. I was wondering what the best solution might be for doing this. The query will use an SQL LIKE type of request to say something like “iPhone case”, “6, 7” but not “5”, in “rubber” but not “red, green or blue”. The field being searched is a varchar(1000) type. This type of query is ok when you have small quantity of data but gets slower and slower the more data you add as the database (mysql) is unable to use an index. I appreciate that their are FULLTEXT type indexes but are more interested in what others have done when not using a FULLTEXT field. Thanks in advance for any feedback / thoughts.

2

How much data to you expect? How fast has the query been in your tests? If something gets too slow you have to change it.

I’m using Valentina and have a text field, which contains a lot of text. A like search was VERY slow. Now I’m using an SQLIte fulltext index for this. This makes such a difference.

3

Perhaps, if you are able, it would be best to break the data into relatable fields. Of course this can only be done if all the “text” strings currently in the database are in a format that can accuratly be parsable.
Doing indexed search is usually much faster.

it sounds like you current query might be something like this

SELECT * FROM mytable WHERE myField like 'iPhone case" AND myfield like "6,7" AND myfield NOT like '5' etc. etc.

instead of

SELECT * FROM mytable WHERE field1 = 'iPhone case' AND field2 = '6,7'  // assuming '5' is also in field 2 we don't need to check

These also ‘assume’ that either the text being searched is a case sensitive match, OR that the table has the “COLLATE NOCASE” applied.

another option
create a 2nd table that contains all the WORDS and a pointer of some kind back to the main record

SELECT a.* 
   FROM myTable a, wordlist b
where a.pointer = b.pointer
     and (b.word in('iphone case','6,7')
     and (b.word not in('5')