[SQL] emulating sub-queries and search prformances

[Antoine Beyeler]

Hello,


As I understand it, sub-queries are not implemented yet. I devised a 
way to work around that, but it is not working well and may illustrate 
a possibility to further improve search performances of Valentina.

In short, I'm trying to make indexed text search with a multi-word 
query, like 'old man', and I tried this approach:
(Mark is a boolean field. Text is a VDK_Text field. I use method to 
achieve case-unsensitiveness, but it is not illustrated here for the 
sake of simplicity.)

UPDATE table SET mark = false WHERE mark = true
UPDATE table SET mark = true WHERE text = 'old'
UPDATE table SET mark = false WHERE mark = true AND NOT text = 'man'

At this point, all records that contains 'old' and 'man' are "marked" 
to true. This process take much less than 1 sec on a 18000 records 200 
MB database. My idea was that such preprocessing would greatly increase 
performance for this linear search:

SELECT * FROM table WHERE mark = true AND text LIKE 'old man'

With my database, I have 199 records marked as true, and 138 
effectively containing 'old man', but the above query takes exactly the 
same time that this one (that is about 6 sec):

SELECT * FROM table WHERE text LIKE 'old man'

In short, the preprocessing is useless.

Do you think that the search with marked records could be improved?? I 
think so, since the search on WHERE mark=true is _very_ fast (less than 
a 10th of sec on my setup) and returns far less records than the total 
count (199 vs 18000). But it seems that this fact is not taken in 
account in your search.


Any idea??

Best regards,
Antoine Beyeler