5
\$\begingroup\$

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 ?

\$\endgroup\$
10
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ I tried to do this with REGEXP function in the query but it took the same amount of time than before (around 1 second), because for each word i have to do an sql query. Maybe i forgot to precise that the "text" column is made of sentences and not only one word ! \$\endgroup\$ – TmSmth Apr 30 '18 at 20:27
  • 1
    \$\begingroup\$ @TmSmth The entire purpose of databases is to be able to do stuff like this efficiently. As long as you index the column, the query will be fast and remain fast even if your database gets large. \$\endgroup\$ – Nathan Merrill May 1 '18 at 2:02
  • \$\begingroup\$ @NathanMerrill When you say index the column you mean having a primary key in the table or make the "text" column the index of the table ? So is it still efficient if the field has several word instead of one ? Is it normal it takes between 1 and 2 second then when i try to filtering at the database level ? \$\endgroup\$ – TmSmth May 1 '18 at 10:59
  • 1
    \$\begingroup\$ Ok, so after some research, it appears that you can do it with an index only if your query is of the form text LIKE "word%" (and is case-insensitive). That said, SQLITE has a module called FTS that does what you want (and fast) \$\endgroup\$ – Nathan Merrill May 1 '18 at 12:51
  • \$\begingroup\$ Ok, i've just tried this, now the entire process takes 2 seconds ! I changed the accepted answer. \$\endgroup\$ – TmSmth May 2 '18 at 9:57
6
\$\begingroup\$
  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.

\$\endgroup\$
  • \$\begingroup\$ Well that was simple ! I don't need regex as i clean my text before actually. So now it takes 10 seconds instead of 200, thanks ! \$\endgroup\$ – TmSmth Apr 30 '18 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.