CODEWITHSUNDEEP
pythonpyqt4
s.jor.ibra
s.jor.ibra

Reputation: 337

decreasing time for search from big database in python

I have a big database with 10000 id When I search for a name from my database, it takes a long time, and I need a way to decreasing the time for shortest time ?? I am using this way :

while (query.next()):
              line3 = self.lineEdit_4.text()
              query.exec_("SELECT * FROM test WHERE name LIKE '%s';" % ("%"+line3+"%"))
              model = QSqlTableModel()
              model.setQuery(query)
              model.select()
              self.tableView.setModel(model)

Upvotes: 1

Views: 142

Answers (1)

Larry Lustig
Larry Lustig

Reputation: 50998

Oh, wait. Original answer is below, but from looking at your code, it appears you may be re-executing your query for every row in the result.

You need to show us the code above your while statement that originally creates and populates query.

Original answer:

Your search is not optizable using an index because you have a wildcard character at the start of your search term. You can do the following:

  1. Remove the wildcard from the beginning of the search and require the user to enter the first few characters of the name (not characters anywhere in the name). You will need to make sure there is an index on the name column.

  2. Apply an additional criteria to limit the number of names the SQL statement must search (post code, for instance). You will need to make sure there's an index on the other column you're searching.

  3. Investigate the user of full text indexing and searching.

This is not actually a Python or a QT question, but rather a question involving whatever SQL database you're sending your query to.

Finally, your code will fail if the name the user enters contains an apostrophe (for instance, the name O'Reilly) and is in danger of SQL injection attack. You should switch to a parameterized query, something like the following (I don't know the PyQT interface in particular):

 query.exec_("SELECT * FROM test WHERE name LIKE ?;" ("%"+line3+"%"))

Upvotes: 4

Related Questions