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 is my "text" field):
for word in wordList: for row in db: hasFound = re.compile(r"\b"+word.lower()+"\\b") if hasFound.search(row): compteur += 1 dicoVolume[word] = compteur
Is there a way to search faster ?