Searching for a word in a list of tuple extracted from a db via SQL

Question

Python 3.6 and Sqlite3.

I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have to return the number of entry in the database with the word in the "text" field. Currently, it takes around 1 second per word to do that. It's fine for 200 words but the list and the db will grow up in the future so i would like to optimize the speed. Here is how i proceed for now (I won't paste the entire code which is a bit too long but important part but if you need more i'll do it !).

I extract the data from the db with this line and i obtain a list of tuple, each tuple is a row in the db :

db = cursor.execute("select * from mydata").fetchall()

Then I loop on the list of word and i'm looking for the word in the db list like this (row[6] is my "text" field):

for word in wordList:
    for row in db:
        hasFound = re.compile(r"\b"+word.lower()+"\\b")
        if hasFound.search(row[6]):
            compteur += 1
    dicoVolume[word] = compteur

Is there a way to search faster ?

Answer 1

You could try doing the filtering at the database level. Something like:

sql = "SELECT COUNT(*) FROM mydata WHERE myTextColumn MATCH %s"
for word in wordList:
    db = cursor.execute(sql, word).fetchall()
    dicoVolume[word] = db[0][0]

This would have the same word boundary caveats mentioned by Ludisposed, but it would save retrieving the entire table. Whether it's faster will probably depend on the relative sizes of the table and the word list, and if the column has an index it can use.

Edit: To use full text indexes with SQLite, it seems you need to create the table as an FTS table, and then do your searches using the MATCH command:

CREATE VIRTUAL TABLE mydata USING fts3(content TEXT);
INSERT INTO mydata VALUES ('some test data');
SELECT count(*) FROM mydata WHERE content MATCH 'test';

SQL Fiddle: http://sqlfiddle.com/#!7/fd6c5/8

Answer 2

1. You should remove the re.compile from the second for loop, this will save some time. You only have to compile once per word in the wordlist

2. Regex is really slow, you could use the in keyword instead however there are some tricks/risks involved with this approach

   >>> import re
   >>> sentence = "I plhave some nice sentences"
   >>> word = "have"
   >>> print word in sentence
   True
   >>> word2 = " have "
   >>> print word2 in sentence2
   False
   >>> print sentence.startswith(word) or sentence.endswith(word)
   False
   >>> print re.search("\b{0}\b".format(word), sentence)
   None
   

   I am making the assumption that the word is in between spaces, but this could not always be the case! You could have have. or have?. Or it could be at the start or end, and not have the double space. In short this is rather error prone.